Formula to Extract Numbers from an Alphanumeric String

[gonesalsa]

Hello
I'm wondering, if there is formula to extract numbers from an alphanumeric string. The numbers will be 5 digits, but the placement within the string will vary. I was using MID, but I have to keep changing the formula based on the placement of the numbers in the string and it's not very efficent. Thanks!

Examples are:
[TABLE="width: 227"]
<tbody>[TR]
[TD]22047 - Cjljj
S022509 Chhheefe 5/12/14
Self Pay # 022597 jjjkjkj
jljlj cheque #022378
[TABLE="width: 170"]
<colgroup><col style="width: 170pt; mso-width-source: userset; mso-width-alt: 8265;" width="226"> <tbody>[TR]
[TD="width: 226, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 170"]
<colgroup><col style="width: 170pt; mso-width-source: userset; mso-width-alt: 8265;" width="226"> <tbody>[TR]
[TD="width: 226, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 227"]
<tbody>[TR]
[TD]

[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]


[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Momentman]

I the formulas above are to handle E, 123e2 is still there..

That's a valid point!

Maybe a nested substitute for "E" and "e"

Excel 2010
	A	B
1	22047 - Cjljj	22047
2	S022509 Chhheefe 5/12/14	22509
3	Self Pay # 022597 jjjkjkj	22597
4	jljlj cheque #022378	22378
5	123e4 - lf Pay # 022597 jjjkjkj	22597
Sheet13

Cell Formulas
Range	Formula
B1	{=MID(A1,MATCH(TRUE,MID(SUBSTITUTE(SUBSTITUTE(A1,"E"," "),"e"," "),ROW(INDIRECT("1:" &LEN(A1)-4)),5)*1>=10000,0),5)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MikeWx]

A slight modification, also an array formula using CTRL SHIFT ENTER, will handle the lower case e:

=MID(A2,MATCH(TRUE,MID(SUBSTITUTE(UPPER(A2),"E"," "),ROW(INDIRECT("1:" &LEN(A2)-4)),5)*1>=10000,0),5)

 

[Cauna in Boston]

I the formulas above are to handle E, 123e2 is still there..

I have been trying to apply Momentum formula to remove the numbers from a string.

{=MID(A1,MATCH(TRUE,IF(LEFT(MID(A1,ROW(INDIRECT("1:" &LEN(A1)-4)),5),1)<>"0",IF(LEFT(MID(A1,ROW(INDIRECT("1:" &LEN(A1)-4)),5),1)<>" ",ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" &LEN(A1)-4)),5)))),0),5)}
Examples Examples of the strings in a cell are:

10.5 oz
50#
6.5
35 lbs

The formula works for 10.5 oz but not 50# or 6.5. Is there a modification I could make to the formula to get it to work with the above examples

Thanks
Cauna in Boston

 

[Rick Rothstein]

I have been trying to apply Momentum formula to remove the numbers from a string.

{=MID(A1,MATCH(TRUE,IF(LEFT(MID(A1,ROW(INDIRECT("1:" &LEN(A1)-4)),5),1)<>"0",IF(LEFT(MID(A1,ROW(INDIRECT("1:" &LEN(A1)-4)),5),1)<>" ",ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" &LEN(A1)-4)),5)))),0),5)}
Examples Examples of the strings in a cell are:

10.5 oz
50#
6.5
35 lbs

The formula works for 10.5 oz but not 50# or 6.5. Is there a modification I could make to the formula to get it to work with the above examples

As long as your values look like what you posted (a number followed by either a space or dimension text), then this should work for you...

=-LOOKUP(0,-LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

Note: Enter this formula normally (it is not an array formula).

 

[Cauna in Boston]

As long as your values look like what you posted (a number followed by either a space or dimension text), then this should work for you...

=-LOOKUP(0,-LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

Note: Enter this formula normally (it is not an array formula).

Hi Rick,

The formula worked perfectly. You are awesome.

Thank you
Have a good day.