Stripping Out Characters

[Amy E. Baggott]

We want to create unique URL redirects for each of our exhibitors to link to
a registration site for our show. We figured we'd use their company name,
but our web person says we need to strip out anything from the company name
that isn't a letter (a-z, A-Z) or a numeral (0-9). Is there an easy way to
automate this so I don't have to do 1800 of them by hand?

 

[Ken Snell \(MVP\)]

You could use a function that would strip out such characters:

Public Function KeepOnlyNumbersLetters(strOriginal As String) As String
Dim blnOKToUse As Boolean
Dim lngLong As Long, lngLoop As Long
Dim strTemp As String, strChar As String
strTemp = ""
For lngLong = 1 To Len(strOriginal)
strChar = Mid(strOriginal, lngLong, 1)
blnOKToUse = False
For lngLoop = Asc("A") To Asc("Z")
If blnOKToUse = False Then
If UCase(strChar) = UCase(Chr(lngLoop)) Then _
blnOKToUse = True
End If
Next lngLoop
For lngLoop = 0 To 9
If blnOKToUse = False Then
If strChar = CStr(lngLoop) Then _
blnOKToUse = True
End If
Next lngLoop
If blnOKToUse = True Then strTemp = strTemp & strChar
Next lngLong
KeepOnlyNumbersLetters = strTemp
Exit Function
End Function


This function could be called from an update query, or from VBA code, or
from a macro.

 

[Dirk Goldgar]

In

We want to create unique URL redirects for each of our exhibitors to
link to a registration site for our show. We figured we'd use their
company name, but our web person says we need to strip out anything
from the company name that isn't a letter (a-z, A-Z) or a numeral
(0-9). Is there an easy way to automate this so I don't have to do
1800 of them by hand?

Here's function you could use to strip the unwanted characters from each
name:

'----- start of code -----
Function StripAllButAlphanumerics(varOldNumber As Variant) As String

' Removes all but alphabetic and numeric characters in a string
' Author: Dirk Goldgar

Dim i As Integer
Dim intLength As Integer
Dim strThisCharacter As String
Dim strOldNumber As String
Dim strNewNumber As String

strOldNumber = varOldNumber & vbNullString

intLength = Len(strOldNumber)

strNewNumber = vbNullString

For i = 1 To intLength
strThisCharacter = Mid(strOldNumber, i, 1)
Select Case Asc(strThisCharacter)
Case 48 To 57, 65 To 90, 97 To 122
strNewNumber = strNewNumber & strThisCharacter
End Select
Next i

StripAllButAlphanumerics = strNewNumber

End Function
'----- end of code -----

Exactly how you would use it depends on what you need to do, but I could
see you calling it in a query.

 

[Lance]

Just slung this togther.. tested only with a couple simple sample strings.
But should work. Although I could have sworn there was an isalphanumeric
function somewhere..

instring = the string you want to strip
replace_string = what you want to replace the invalid characters with, use
"" for blank

Function alpha_numeric_only(instring As String, replace_string As String) As
String
Dim i As Integer
Dim AN As Integer
Dim BS As String

For i = 1 To Len(instring)
AN = Asc(Mid(instring, i, 1))
If Not (AN = 32 Or (AN >= 48 And AN <= 57) Or (AN >= 65 And AN <= 90)
Or (AN >= 97 And AN <= 122)) Then
BS = BS & replace_string
Else
BS = BS & Mid(instring, i, 1)
End If
Next i
alpha_numeric_only = BS
End Function

 

[Guest]

We want to create unique URL redirects for each of our exhibitors to link
to
a registration site for our show. We figured we'd use their company name,
but our web person says we need to strip out anything from the company
name
that isn't a letter (a-z, A-Z) or a numeral (0-9). Is there an easy way
to
automate this so I don't have to do 1800 of them by hand?