Finding First Alphabetical Character in a String

[meangene]

What command would I use to find the position of the first alphabetical
character in a string? Example: 145-54A = 7, 12-54CG = 6. Would use InStr if
I knew how to indicate "any alphabetical character" in the string2 position.

 

[John Spencer]

You would need a custom function to do this. There is no built-in function.

Paste the following into a VBA module and save it. Then call it as you would
any other function.

Public Function fFirstLetterPosition(strIN As Variant) As Integer
Dim intReturn As Integer
Dim iCount As Integer

For iCount = 1 To Len(strIN & "")
If Mid(strIN, iCount, 1) Like "[A-z]" Then
intReturn = iCount
Exit For
End If
Next iCount

fFirstLetterPosition = intReturn
End Function


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[meangene]

Sorry didn't get back - this did the trick - thanks!

You would need a custom function to do this. There is no built-in function.

Paste the following into a VBA module and save it. Then call it as you would
any other function.

Public Function fFirstLetterPosition(strIN As Variant) As Integer
Dim intReturn As Integer
Dim iCount As Integer

For iCount = 1 To Len(strIN & "")
If Mid(strIN, iCount, 1) Like "[A-z]" Then
intReturn = iCount
Exit For
End If
Next iCount

fFirstLetterPosition = intReturn
End Function


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

What command would I use to find the position of the first alphabetical
character in a string? Example: 145-54A = 7, 12-54CG = 6. Would use InStr if
I knew how to indicate "any alphabetical character" in the string2 position.