String function to detect firt alphabetical character

[exaccess]

Hi All,
I have a string where alphabetical and numerical characters are mixed up. I need a string function where I can find the index of the first non-numerical character. I tried with the mid function but could not get it to work. Help please.

 

[Uncle Gizmo]

Please post your code.

 

[vbaInet]

I can see that Uncle Gizmo has requested to see your code, but in the meantime, here are some pointers:

1. Loop through each character using the Mid() function
2. Negate the IsNumeric() function (i.e. Not IsNumeric() or IsNumeric() = False) to check whether that character is of type Number.
3. If it is, then the loop index would be the index of the first occurrence of a non-numeric alphabet.

An alternative solution would be to look into Regular Expressions.

 

[Uncle Gizmo]

An alternative solution would be to look into Regular Expressions.

Hi VB (Is it ok to shorten?)

I am asking a question borne of ignorance... (I can't be bothered to learn RegX)

I thought RegX was for pattern matching?

So I ask will it return the index?

 

[vbaInet]

Sure! There's a FirstIndex property of the Matches collection in RegEx that you can use.

 

[MarkK]

Check out the little known Val() function (provided by VBA.Conversion), which returns all leading numeric characters. Once you have those, you'll know how long they are, and you can directly target the first non-numeric character.

Private Function GetIndexFirstNonNumeric(text As String) As Long
    GetIndexFirstNonNumeric = Len(Val(text)) + 1
End Function

But this fails if there are spaces in your number. If that might occur, you may need to Replace() those spaces, or something.

 

[MarkK]

Actually, in some cases you might need to do this . .

Private Function GetIndexFirstNonNumeric(text As String) As Long
    GetIndexFirstNonNumeric = Len(CStr(Val(text))) + 1
End Function

 

[vbaInet]

Actually Mark, I think the poster wants to find the index of the first non-numeric character so it could anywhere, i.e. start, middle, or end. Val() will fail if the first character is non-numeric.

 

[gemma-the-husky]

something like this?

Function test(mystrg As String) As Long
Dim x As Long
     x = 1
 'first part of following is to make sure the loop stops
 'if there are only numeric chars
     While x <= Len(mystrg) And IsNumeric(Mid(mystrg, x))
          x = x + 1
    Wend
     If x > Len(mystrg) Then  'only numeric
        test = 0
    Else
        test = x
    End If
End Function

 

[MarkK]

Using recursion . . .

Private Function GetIndexFirstNonNumeric(text As String, Optional start As Integer) As Integer
[COLOR="Green"]    'if the character at the start index is numeric . . .[/COLOR]
    If IsNumeric(Mid(text, start + 1, 1)) Then
[COLOR="Green"]        'then advance the start index and try again[/COLOR]
        GetIndexFirstNonNumeric = GetIndexFirstNonNumeric(text, start + 1)
    Else
[COLOR="Green"]        'else, return the current start index[/COLOR]
        GetIndexFirstNonNumeric = start
    End If
End Function

 

[vbaInet]

More like:

Function FirstNumOcc(TheVal As String, Optional Index As Long) As Long
    If IsNumeric(Left(TheVal, 1)) Or TheVal = "" Then
        FirstNumOcc = IIf(TheVal = "", 0, Index + 1)
    Else
        FirstNumOcc = FirstNumOcc(Mid(TheVal, 2), Index + 1)
    End If
End Function

FirstNumOcc("hello World 2")

Don't put anything in the second parameter.

 

[MarkK]

vbaInet, you might have misread the requirement. My understanding is it's the index of the first non-numerical character.

find the index of the first non-numerical character

Given the string "hello world 2" I would expect the result to be zero. Am I wrong?

 

[vbaInet]

Mark, somehow (I don't know how, don't ask), I read it to mean the "first occurrence of a numerical character" Thanks for pointing it out. You should have called it out ages ago

In that case the Val() code you gave earlier.

Or just for the fun of it, if I were to change my recursive call, I'll only change this line:

If [COLOR="Red"]Not[/COLOR] IsNumeric(Left(TheVal, 1)) Or TheVal = "" Then

And the call:

FirstNonNumOcc("123 hello world")

Should yield 4, in line with the Instr() function.

 

[exaccess]

Sorry Folks,
I had to work on an urgent problem where there was a crush in real time system. Now that I am back I looked at the case and this last suggestion paves the way to the solution. Thanks a lot folks.