Find Numeric sign in a string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nir - 06 Nov 2006 17:25 GMT

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

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

thanks

Reply to this Message

Ron Rosenfeld - 06 Nov 2006 17:46 GMT

>Hi,
>I need to find the position of the first Numeric sign in a string.
[quoted text clipped - 4 lines]
>
>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

Reply to this Message

Nir - 06 Nov 2006 17:59 GMT

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

> >Hi,
> >I need to find the position of the first Numeric sign in a string.
[quoted text clipped - 14 lines]
> XL will place braces {...} around the formula.
> --ron

Reply to this Message

Bob Phillips - 06 Nov 2006 18:29 GMT

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

Signature

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
[quoted text clipped - 20 lines]
> > XL will place braces {...} around the formula.
> > --ron

Reply to this Message

Lori - 06 Nov 2006 18:40 GMT

For a non array formula you could use:

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

> Thanks Ron it works,
> Is there a simpler way, i need to get this into a macro routine, I dont know
[quoted text clipped - 20 lines]
> > XL will place braces {...} around the formula.
> > --ron

Reply to this Message

Ron Rosenfeld - 06 Nov 2006 19:18 GMT

>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

Reply to this Message