This is a function I use for this purpose:
Function MakeValidRangeName(ByVal strRangeName As String, _
Optional strAddIfLeadingNumericsOrDots As String
= "_", _
Optional strAddIfTrailingNumerics As String =
"_", _
Optional bRemoveLeadingNumericsAndDots As
Boolean, _
Optional bRemoveTrailingNumerics As Boolean, _
Optional strSpaceReplace As String = "_") As
String
If Len(strRangeName) = 0 Then
MakeValidRangeName = "No_Name_Provided"
Exit Function
End If
'1. not longer than 255 characters, but allow for added leading and/or
trailing "_"
'----------------------------------------------------------------------------------
strRangeName = Left$(strRangeName, 253)
'2. clear all these. Note that the characters \ and . are valid
'--------------------------------------------------------------
strRangeName = ClearCharsFromString(strRangeName,
"!£$%^&*()-+={}[]:;@'~#|<,>?/")
'3. to avoid range names starting with numerics or dots (trailing dotsare
fine)
'-------------------------------------------------------------------------------
If bRemoveLeadingNumericsAndDots Then
strRangeName = ClearCharsFromString(strRangeName, ".0123456789",False,
True)
Else
If InStr(1, ".0123456789", Left$(strRangeName, 1), vbBinaryCompare) > 0
Then
strRangeName = strAddIfLeadingNumericsOrDots & strRangeName
End If
End If
'4. to avoid range names ending with numerics
'--------------------------------------------
If bRemoveTrailingNumerics Then
strRangeName = ClearCharsFromString(strRangeName, "0123456789", False, ,
True)
Else
If InStr(1, "0123456789", Right$(strRangeName, 1), vbBinaryCompare) > 0
Then
strRangeName = strRangeName & strAddIfTrailingNumerics
End If
End If
'5. replace spaces with "_" or other specified character
'-------------------------------------------------------
strRangeName = Replace(strRangeName, " ", strSpaceReplace, 1, -1,
vbBinaryCompare)
MakeValidRangeName = strRangeName
End Function
Function ClearCharsFromString(strString As String, _
strChars As String, _
Optional bAll As Boolean = True, _
Optional bLeading As Boolean, _
Optional bTrailing As Boolean) As String
Dim i As Long
Dim strChar As String
ClearCharsFromString = strString
If bAll Then
For i = 1 To Len(strChars)
strChar = Mid$(strChars, i, 1)
If InStr(1, strString, strChar) > 0 Then
ClearCharsFromString = Replace(ClearCharsFromString, _
strChar, _
vbNullString, _
1, -1, vbBinaryCompare)
End If
Next i
Else
If bLeading Then
Do While InStr(1, strChars, Left$(ClearCharsFromString, 1), _
vbBinaryCompare) > 0
ClearCharsFromString = Right$(ClearCharsFromString, _
Len(ClearCharsFromString) - 1)
Loop
End If
If bTrailing Then
Do While InStr(1, strChars, Right$(ClearCharsFromString, 1), _
vbBinaryCompare) > 0
ClearCharsFromString = Left$(ClearCharsFromString, _
Len(ClearCharsFromString) - 1)
Loop
End If
End If
End Function
RBS
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- Hide quoted text -
- Show quoted text -
