Find first non numeric character in string

[Guest]

I want to strip out the first part of a string so that the result starts with
the first non numeric character. The second numeric value (19184 in the
example below) is an id that can vary in length so I can't just use the mid
function as the starting point will vary.

Example:

"1 - 19184 - BP Trowbridge Lodge Service Station"

I want to return "BP Trowbridge Lodge Service Station"

Thanks for any help

 

[Guest]

Is the " - " always going to be there? If so you can look for that.

 

[Guest]

try this:
Public Function GetAlphaString(strMyString) As String
Dim i As Integer
Dim iAsc As Integer
For i = 1 To Len(strMyString)
iAsc = Asc(Mid(strMyString, i, 1))
If (iAsc >= 65 And iAsc <= 90) Or (iAsc >= 97 And iAsc <= 122) Then
GetAlphaString = Right(strMyString, Len(strMyString) - i + 1)
Exit For
End If
Next i
End Function

(or change the if statement to 'select case' - it's easier to read)

jp

 

[Guest]

If the general format of the string is always the same then you could take
all text after the final "-":

Function GetName(strIn As String) As String
'''Returns text after the final "-" in strIn
' Returns "" if no "-" found
Dim iPos As Integer

iPos = InStrRev(strIn, "-")
If iPos = 0 Then
GetName = ""
Else
GetName = Application.Trim(Right(strIn, Len(strIn) - iPos))
End If
End Function

if this won't work and you really want to start with the first alpha
character then use this:

Function GetName2(strIn As String) As String
'''Returns text stating with the first alpha character
' Returns "" if no alpha characters are found
' Note: ASCII "A" = 65, "Z" = 90, "a" = 97, "z" = 122
Dim iPos As Integer, iLen As Integer, iAsc As Integer

iLen = Len(strIn)
For iPos = 1 To iLen
iAsc = Asc(Mid(strIn, iPos, 1))
If (iAsc > 64 And iAsc < 91) Or (iAsc > 96 And iAsc < 123) Then
Exit For
End If
Next iPos

If iPos > iLen Then
'no alpha characters found in string
GetName2 = ""
Else
GetName2 = Application.Trim(Right(strIn, Len(strIn) - iPos + 1))
End If
End Function

To use these functions, code something like this:

Sub Test()
Dim strResult

strResult = GetName2("1 - 19184 - BP Trowbridge Lodge Service Station")
MsgBox strResult
End Sub

Cheers,
Dave