Oct 06 2021 06:04 AM
I would like to insert a function in a worksheet to check whether the Excel Filename contains special symbols such as "!,.*$%"" and if yes, to output 1 otherwise , to sum a range of numbers.
Oct 06 2021 07:03 AM
Any file name contains dot, other symbols in the list are not allowed. That's exactly what you'd like to check?
Oct 06 2021 07:10 AM
@Chan_Tze_Leong There are a couple parts to this and I'm going to assume you have Excel365:
to find the filename (also assumes the file has been saved and I don't think it work with web-based Excel):
=LET(a,CELL("filename"),b,SEARCH("[",a),c,SEARCH("]",a),MID(a,b+1,c-b-1))
next to find out if that name has "special characters" I will assume it will have 1 "." for the file extension (e.g. ".xlsx") and that you are looking for anything that isn't a letter or number or a space:
=LET(in,A1,a,CODE(MID(UPPER(in),SEQUENCE(LEN(in)),1)),b,(a>=CODE("A"))*(a<=CODE("Z"))+(a>=CODE("0"))*(a<=CODE("9"))+(a=CODE(" "))-1,SUM(b)<-1)
Where A1 is the filename and the output is TRUE is there are any non-alphanumeric or space characters (except for 1 period).
Of course these could be combined and all those extra CODE() statements should be replaced with actual values for efficiency, but I thought this would show you what it is doing.
Hope this helps you get toward your goal.