Forum Discussion
Error if filename contains special characters
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.
2 Replies
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.
Any file name contains dot, other symbols in the list are not allowed. That's exactly what you'd like to check?
