Function to look for alpha character

  • Thread starter Thread starter CPK
  • Start date Start date
C

CPK

Is there a function to tell me the position of ANY alpha character in a
String ?

"123456A555"

I would expect 7 to be returned.


What I really need is a way to strip everything to the left of the A, but
the Alpha character can be A to Z and I don't want to write 26
Instr([field],Letter) If thens.

"1234B777" I need "1234"

"1234567X88" I need "1234567" etc.


thanks
 
This example is case-insensitive and will not match accented characters etc.
You can add additional characters to the constant if required.

Public Function PosFirstAlpha(ByVal StringToSearch As String) As Long

Dim lngLoop As Long
Const strcChars As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

For lngLoop = 1 To Len(StringToSearch)
If InStr(1, strcChars, _
UCase$(Mid$(StringToSearch, lngLoop, 1))) <> 0 Then
PosFirstAlpha = lngLoop
Exit For
End If
Next lngLoop

End Function
 
CPK wrote in message said:
Is there a function to tell me the position of ANY alpha character in a
String ?

"123456A555"

I would expect 7 to be returned.


What I really need is a way to strip everything to the left of the A, but the
Alpha character can be A to Z and I don't want to write 26
Instr([field],Letter) If thens.

"1234B777" I need "1234"

"1234567X88" I need "1234567" etc.


thanks

Try issuing the Val function on your data

Val("1234B777")
Val("1234567X88")
 
Back
Top