String function to detect firt alphabetical character (1 Viewer)

exaccess

Registered User.
Local time
Today, 12:55
Joined
Apr 21, 2013
Messages
287
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.
 

vbaInet

AWF VIP
Local time
Today, 11:55
Joined
Jan 22, 2010
Messages
26,374
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

Nifty Access Guy
Staff member
Local time
Today, 11:55
Joined
Jul 9, 2003
Messages
16,244
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

AWF VIP
Local time
Today, 11:55
Joined
Jan 22, 2010
Messages
26,374
Sure! There's a FirstIndex property of the Matches collection in RegEx that you can use.
 

MarkK

bit cruncher
Local time
Today, 04:55
Joined
Mar 17, 2004
Messages
8,178
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.
Code:
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

bit cruncher
Local time
Today, 04:55
Joined
Mar 17, 2004
Messages
8,178
Actually, in some cases you might need to do this . .
Code:
Private Function GetIndexFirstNonNumeric(text As String) As Long
    GetIndexFirstNonNumeric = Len(CStr(Val(text))) + 1
End Function
 

vbaInet

AWF VIP
Local time
Today, 11:55
Joined
Jan 22, 2010
Messages
26,374
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

Super Moderator
Staff member
Local time
Today, 11:55
Joined
Sep 12, 2006
Messages
15,613
something like this?

Code:
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

bit cruncher
Local time
Today, 04:55
Joined
Mar 17, 2004
Messages
8,178
Using recursion . . .
Code:
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

AWF VIP
Local time
Today, 11:55
Joined
Jan 22, 2010
Messages
26,374
More like:
Code:
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

Code:
FirstNumOcc("hello World 2")
Don't put anything in the second parameter.
 

MarkK

bit cruncher
Local time
Today, 04:55
Joined
Mar 17, 2004
Messages
8,178
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

AWF VIP
Local time
Today, 11:55
Joined
Jan 22, 2010
Messages
26,374
Mark, somehow (I don't know how, don't ask), I read it to mean the "first occurrence of a numerical character" :D 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:
Code:
If [COLOR="Red"]Not[/COLOR] IsNumeric(Left(TheVal, 1)) Or TheVal = "" Then
And the call:
Code:
FirstNonNumOcc("123 hello world")
Should yield 4, in line with the Instr() function.
 

exaccess

Registered User.
Local time
Today, 12:55
Joined
Apr 21, 2013
Messages
287
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.
 

Users who are viewing this thread

Top Bottom