ExcelBanter - View Single Post - taking out all \/?*[] so input can be name of worksheet

Ron Rosenfeld

On Wed, 4 Jun 2008 01:25:00 -0700 (PDT), wrote:

Hi!
I'm reading in a CSV file. I want to use one of the fields as the
name of the WorkSheet. The file is from different clients and the
field is free text. I've come across names with ** or \. Do I have
to use the SUBSTITUTE function 6 times or is there a replaceText
function?
Thanks,
Mechi

How about using Regular Expressions. You could run something like the
following function on your chosen field:

========================
Function wsNAME(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[\\/:*?<|]+"
wsNAME = re.Replace(str, "")
End Function
==========================

--ron