Function for finding first numeric in a text string

[Jack Schweikhard]

I have a text string, i.e. MAIIA0045879

the first numberic charcter, from the left, would be 0 (zero) at position 6

Is there a function that will find the first numeric character position in this string

I am accustomed to usint the "Find" function but I don't know any wild card that could be used for a numeric.

 

[Juan Pablo González]

How about this ?

Using MoreFunc.xll

=MIN(IF(ISNUMBER(SETV(FIND({1,2,3,4,5,6,7,8,9,0},A2))),GETV()))

array entered.

Another version, which I feel Aladin is going to like

=MATCH(TRUE,ABS(CODE(MID(A2,ROW($A$1:INDEX(A:A,LEN(A2))),1))-52.5)<5,0)


array entered as well. (No use of MoreFunc in there...)

 

[IML]

Just to prove Juan puts the V in MVP (not that there was any doubt), I was going down this hitious road:

=MID(A1,MIN(IF((ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0)*ROW(INDIRECT("1:"&LEN(A1)))),ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0)*ROW(INDIRECT("1:"&LEN(A1))))),1)+0

 

[eliW]

I have a text string, i.e. MAIIA0045879

the first numberic charcter, from the left, would be 0 (zero) at position 6

Is there a function that will find the first numeric character position in this string

I am accustomed to usint the "Find" function but I don't know any wild card that could be used for a numeric.

Also:

=MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1)),"",FIND({1;2;3;4;5;6;7;8;9;0},A1)))

Not Array entered, nor MoreFunction needed.

Eli

 

[Juan Pablo González]

Also:

=MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1)),"",FIND({1;2;3;4;5;6;7;8;9;0},A1)))

Not Array entered, nor MoreFunction needed.

Eli

I'd propose then this

=MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2)))

which seems more logical (A number is the "expected" result, not the error)... but, MoreFunc would provide far more efficency, since the FIND() is only evaluated once...

 

[rrdonutz]

Thought I'd throw this in the mix. I propose it only if you are certain that your text string contains at least 1 number. If the text string is in cell A2, then the following non-array formula will also work:

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

It can be revised to handle "the no numbers in the string" case as follows (& using MoreFunc):

=IF(SETV(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&{0,1,2,3,4,5,6,7,8,9})))>LEN(A2),0,GETV())

However, it appears that Eli's (or Juan's variant) is probably a better solution to handle all possible cases.

--Tom