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