Find Numeric sign in a string

[Guest]

Hi,
I need to find the position of the first Numeric sign in a string.

"AA01" = 3
"AB2E3" =3
"2AAFT3" =1

thanks

 

[Ron Rosenfeld]

Hi,
I need to find the position of the first Numeric sign in a string.

"AA01" = 3
"AB2E3" =3
"2AAFT3" =1

thanks

One way:

With data in A2, *array-enter* the following formula:

=MATCH(TRUE,ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0)

To enter an **array** formula, hold down <ctrl><shift> while hitting <enter>.
XL will place braces {...} around the formula.
--ron

 

[Guest]

Thanks Ron it works,
Is there a simpler way, i need to get this into a macro routine, I dont know
how to implement the {} with code.

TIA

 

[Bob Phillips]

Sub FirstNumeric()
Dim i As Long
Dim sTest

sTest = "AA701"

For i = 1 To Len(sTest)
If IsNumeric(Mid(sTest, i, 1)) Then
MsgBox Mid(sTest, i, 1)
Exit For
End If
Next i
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks Ron it works,
Is there a simpler way, i need to get this into a macro routine, I dont know
how to implement the {} with code.

TIA

 

[Lori]

For a non array formula you could use:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B1&9876543210))

 

[Ron Rosenfeld]

Thanks Ron it works,
Is there a simpler way, i need to get this into a macro routine, I dont know
how to implement the {} with code.

TIA

If you are doing this within a macro, use Bob's routine.
--ron