How to extract the first number from a excel cell containing multiple numbers

[narayani]

Hi,

I need to extract the first number from a excel cell containing more than one number. For eg, in this case,

"abc 1234 pc5678999" - i want to show only 1234 in the next cell. I applied one formula which I got from one of the forums - =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$18),1)),0),COUNT(1*MID(A1,ROW($1:$18),1))) but this does not work in this case as there is another set of number given after 1234. Can anyone help me as to how the above formula can be modified or is there any other formula which I can apply?

<tbody>
</tbody>

 

[Special-K99]

The following works fine for me

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1))
Its an array formula, use Ctrl-Shift-Enter.

 

[Momentman]

The following works fine for me

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1))
Its an array formula, use Ctrl-Shift-Enter.

But this will fail when you have a string of more than 9 consecutive numbers for example

with "abc 4567893433356 fgh", the formula gives 45678

 

[hiker95]

Special-K99,

Nicely done - one for my archives - thanks.

 

[Jonmo1]

Try

=LOOKUP(9.99999999999999E+307,MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(A1:INDEX(A:A,LEN(A1))))+0)

 

[narayani]

Hi,

Even the lookup formula does not work in all cases. In the following case - 'CONNER SJB 446 pc123456' - I want to get 446 as my result and the formula gives answer correctly. But in this case 'TIERNEY APM&SJ SJB 776 so44485'- it does not give me 776. Can you please explain me as to why this formula works weirdly

 

[XOR LX]

Hi,

Even the lookup formula does not work in all cases. In the following case - 'CONNER SJB 446 pc123456' - I want to get 446 as my result and the formula gives answer correctly. But in this case 'TIERNEY APM&SJ SJB 776 so44485'- it does not give me 776. Can you please explain me as to why this formula works weirdly

I get 776 with Jonmo's formula.

Regards

 

[narayani]

Hi,

I tried the formula by putting this data now in row A1 and the formula worked as you have said. But if this data 'TIERNEY APM&SJ SJB 776 so44485' is entered in row A5 onwards, it does not give any result.

Does it mean that the formula needs to be modified or something?

 

[XOR LX]

I don't understand what you mean. If the formula which refers to e.g. A1 is in say B1 then, when copied down to B2, B3, etc., it will give respective results for the entries in A2, A3, etc.

Regards

 

[narayani]

I have a set of data to work as given below and I need to get the first number in the next cell. Just a example of this data is as below:

ABC SJB302 7528051084326015SO 302 ABC SJB 446 446 ABC N 5PL F SJB 466 #N/A ABC APM&SJ SJB 776 5944485374322016SO 776 ABC CG SJB 9 0175595584322014SO #N/A ABC CARMEL SJB/405 FP14097O51938042 #N/A ABC SJB302 7528051084326015SO #N/A ABC N L SJB453 36023546483743000N #N/A ALS SJB8 RP4671464307371300 #N/A A BC 930 RP4671663922856500 #N/A ABC-SM 458 00156460632BBBVHBP #N/A ABC 798 RP4672163833173200 #N/A ABC PTA 00453599 453599 <tbody> </tbody>

<tbody>
</tbody>

The first column represents the data and the second column shows the results which I get on applying the lookup formula.
This formula works only for few cells as shown above and it does not give me the results for all the cells. However if each of the above data is put in cell A1 individually, it gives the correct result.