PDA

View Full Version : Solved: WS Name function



fb7894
12-13-2008, 08:13 PM
I have a routine that will create multiple worksheets within a workbook and name these worksheets based upon certain data.

However if an invalid character is in the data, my code will crash.

Does anyone have a function which will strip a variable of unusable characters for naming a worksheet?

Example:
"This/Name" is invalid. I'd like to turn this into "This-Name".

Thanks

Simon Lloyd
12-14-2008, 01:22 AM
How are the invalid characters picked up? you can always use the worksheet function SUBSTITUTE like this:
Application.WorksheetFunction.Substitute(Range("A1").Value, "/", "-")

fb7894
12-14-2008, 09:23 AM
Thanks for the reply. I'm looking for a function that replaces any unusable character. For instance, you can't use ? > @ : (I'm guessing at these).

Does anyone have a function that identifies ALL non-usable characters for naming a worksheet?

THanks again.

nst1107
12-14-2008, 09:45 AM
Here are the characters that Excel does not allow in worksheet names: * [ ] \ : / ?
If you did as Simon suggested, and used 7 Substitute functions, you would have your filter.

Cyberdude
12-14-2008, 10:15 AM
That's quite astonishing. Curiously enough, fb7894, the only illegal character in your example list is the colon (:). I was amazed that just about any character you want to use is legal for a sheet name. nst1107 furnished the list of unusables which I was unable to find execept by trying each one.

fb7894
12-14-2008, 05:02 PM
I was amazed that just about any character you want to use is legal for a sheet name.
Me too. I guess I just assumed the list of illegal characters would be lengthy. That's what I get for assuming. THanks for the help everyone!