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

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

"A good example is twice the value of good advice."


"Snowsride" wrote:

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

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

"Charles Chickering" wrote:

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

"A good example is twice the value of good advice."


"Snowsride" wrote:

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

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


"Snowsride" wrote:

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