Find Numeric sign in a string

N

Nir

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

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

thanks
 
R

Ron Rosenfeld

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

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

thanks
Click to expand...

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
 
N

Nir

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
 
B

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)

Nir said:
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


Click to expand...
 
L

Lori

For a non array formula you could use:

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

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
Click to expand...

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