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

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

thanks

On Mon, 6 Nov 2006 09:25:02 -0800, Nir wrote:

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

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


"Ron Rosenfeld" wrote:

On Mon, 6 Nov 2006 09:25:02 -0800, Nir wrote:

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

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" wrote in message
...
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


"Ron Rosenfeld" wrote:

On Mon, 6 Nov 2006 09:25:02 -0800, Nir
wrote:

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

For a non array formula you could use:

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



Nir wrote:

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


"Ron Rosenfeld" wrote:

On Mon, 6 Nov 2006 09:25:02 -0800, Nir wrote:

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

On Mon, 6 Nov 2006 09:59:02 -0800, Nir wrote:

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