Find illegal file names

Hello

I have a column of strings. These strings will be used to create file names and therefor must not contain < > : " / \ | ? *.
Can a formula be made to check if any of the strings contain any of these characters?

Hi

Would it not be quicker and easier to use find replace

Chris

Try this function which return "True" if any of the invalid character exists in function argument "FileName"

Please Login or Register  to view this content.

_________________________________
aelgadi

>>> If I helped, Don't forget to add to my reputation (click on the star at left bottom of the post)
>>> If satisfied with a solution, don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

Jacc,

Attached is an example workbook based on the criteria you described.
Column A has had this conditional format formula applied to it:

Please Login or Register  to view this content.

All cells in column A with an illegal character are now red highlighted. Would something like that work?

As a side note, I agree with dogberry's suggestion also. A find/replace should work just fine.

Thanks for the replies guys!
Dogberry, I agree with you, for many purposes the f&r is effective and fast. What I failed to mention was that this is for an application that others will use. It's up to the user to put the correct name on things as these names will also be used for pictures and so on. I just want to warn them. There is nothing to replace with and if I replace with nothing I could end up with duplicates. Hmm... I do already have a duplicate check...

aelgade, your function looks nice and clean. However, my dataset is about 1000 to 2000 lines and I believe a VBA function would struggle to keep up the pace with the Excel built in functions. I believe there is an InStr function in VBA that might speed things up a bit, though.

tigeravatar, your function seems to do the trick. I just need a moment here to figure out what is going on (as always with Excel functions ). In the process I will turn it into a regular function in an extra column. The reason is that I read that CF is volatile and will recalc for any reason. My application is slow as it is, so I am a little paranoid with anything taking time . Hold on, that ROW($1:$9) can't be right, the data is all the way to row 18. Or?

I appreciate your inputs, all different angles covered.

Originally Posted by Jacc

Hold on, that ROW($1:$9) can't be right, the data is all the way to row 18. Or?

Seems that it is supposed to be ROW($1:$10) as their are 10 illegal characters.
I tried to dissect the formula into several columns to study it but that just don't work. I guess SUMPRODUCT turns it into an array formula but even made as array formulas the parts don't work alone. How does this thing work?

Sorry, 9 is correct. There was an error and I solved it by changing 9 to 10. Turned out the error was related to having A2 in one place and A1 in the other, or something like that.

I have the formula figured out now.

It works like a charm and it's pretty fast it seems. I made a test on 1000 rows and it was almost instant.
Thanks tigeravatar! It's an impressive formula.
No star for you now, it seems you got the last one.

The formula would have made my day if it wasn't for this: http://www.excelforum.com/excel-gene...to-unhide.html

You're very welcome And yes, you're correct the row ($1:$9) is because there are 9 illegal characters to check for.

Well, it didn't end so well as you can read here: http://www.excelforum.com/excel-gene...to-unhide.html
Formula is brilliant and I can't recreate the error in another workbook with another table. However, it can't be used in that very table because of some weird Excel bug.

In theory it seems that aelgadi's UDF would be faster if it used InStr instead for FileNames longer than 9 characters. I have up to 26 characters.
In real life I'm not gonna bother, I just implemented it and it takes about a second for 1000 lines.
Today's star goes to Kitchener, Ontario!

Hello Jacc,

Tank you Jacc for comming back to me. I know for me there is no more star from you but I'm here to help that's all. You keep mentioning about Instr built-in function so I did it for if this what you did expect.
If you try this please let me know let me know whether it's faster or slower....
Thanks again

Please Login or Register  to view this content.

aelgadi

>>> If satisfied with a solution, don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

Thanks! Since I suggested it I had to do some testing.

Tried with a string length of 141 and 10 000 rows, no illegal characters. CaseSel around 1.2 s, Instring around 0.35 s.
Tried again with a string length of 3, CaseSel around 0.3 s, Instring around 0.25 s.
Booth of them are far quicker than I expected a UDF to be. Are UDF's faster now then in Excel 2003, or is it all the computer? Who knows.

Since I only have about 1000 lines I'm gonna go with CaseSel cause the code looks better.