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>

 

[XOR LX]

Ah, yes. Of course. Jonmo forgot to make one of the references absolute. Should be:

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

and then copied down.

Regards

 

[narayani]

Hi,

Yes it is working now for all the cells, great!!!

Thanks a lot for your help. My work has become much simpler now because of this formula.. Have a nice day!!

 

[hiker95]

narayani,

If the following is your present situation, what number(s) should be the correct result(s) in the YELLOW cells?

Excel 2007
	A	B	C
1	ABC SJB302 7528051084326015SO	302
2	ABC SJB 446	446
3	ABC N 5PL F SJB 466	#N/A
4	ABC APM&SJ SJB 776 5944485374322016SO	776
5	ABC CG SJB 9 0175595584322014SO	#N/A
6	ABC CARMEL SJB/405 FP14097O51938042	#N/A
7	ABC SJB302 7528051084326015SO	#N/A
8	ABC N L SJB453 36023546483743000N	#N/A
9	ALS SJB8 RP4671464307371300	#N/A
10	A BC 930 RP4671663922856500	#N/A
11	ABC-SM 458 00156460632BBBVHBP	#N/A
12	ABC 798 RP4672163833173200	#N/A
13	ABC PTA 00453599	453599
Sheet1

 

[narayani]

Hi,
The answers which i want to get by applying the lookup formula is given in the third column. Only the result in the 3rd row is an exception as I want to get 466 as my result but it will show 5 as it is the first number reflected. However on applying the revised lookup formula, my problem is solved for rest of the cases.

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

<tbody>
</tbody>

 

[Jonmo1]

Thanks a lot for your help. My work has become much simpler now because of this formula.. Have a nice day!!

Glad to help, thanks for the feedback.

Ah, yes. Of course. Jonmo forgot to make one of the references absolute

Thanks, I was gone for the day and couldn't correct it..

 

[XOR LX]

Thanks, I was gone for the day and couldn't correct it..

No worries. Nice solution.

Cheers

 

[István Hirsch]

This formula extracts what you need, including row 3 containing „5PL”:

=LOOKUP(10^10,MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9}&" ",A1&" 0 1 2 3 4 5 6 7 8 9 "))-{0,1,2,3,4,5,6,7,8,9},{1,2,3,4,5,6,7,8,9,10})+0)

Excel Workbook
	A	B
1	ABC SJB302 7528051084326015SO	302
2	ABC SJB 446	446
3	ABC N 5PL F SJB 466	466
4	ABC APM&SJ SJB 776 5944485374322016SO	776
5	ABC CG SJB 9 0175595584322014SO	9
6	ABC CARMEL SJB/405 FP14097O51938042	405
7	ABC SJB302 7528051084326015SO	302
8	ABC N L SJB453 36023546483743000N	453
9	ALS SJB8 RP4671464307371300	8
10	A BC 930 RP4671663922856500	930
11	ABC-SM 458 00156460632BBBVHBP	458
12	ABC 798 RP4672163833173200	798
13	ABC PTA 00453599	453599
14	ABC PTA 00453599DR 123456789	123456789
15	930 ere w 345r	930
Sheet

 

[hiker95]

István Hirsch,

Very nice - thanks - one for my archives.