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


--
Brendan Reynolds
Access MVP


"CPK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

CPK wrote in message <(E-Mail Removed)> :
> 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")

--
Roy-Vidar