extracting numbers from a string of text and numbers

**ScottLor** · 04-05-2013, 04:08 PM

Hi guys,
I am stumped here. I am trying to pull out the numbers from a column that contains text and numbers. The text are serial numbers, and the number part in sandwiched between two clumps of text. However, these clumps of text on either side are not always the same size so just using the =mid function doesnt seem to be working, unless I can tell it to start when it sees the first number, which I can't figure out. The # i am trying to get is sometimes 3 and sometimes 4 numbers long.

Example:

MT411BY
MX623AB
WA460WGL
U420BS
CM1700GA
H710LOCO
M365WT

I would like a column next to this one to read

411
623
460
1700
710
365

Can anyone help with this?

Thanks

**FDibbins** · 04-05-2013, 04:37 PM

Hi

Try this, copied down...
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre

**alansidman** · 04-05-2013, 04:40 PM

Look at this site for a couple of solutions

http://chandoo.org/wp/2012/06/19/ext...om-text-excel/

**ScottLor** · 04-05-2013, 04:47 PM

Ron you are a saint.

**AZ-XL** · 04-05-2013, 04:47 PM

suppose that ytour data in a1 cell

=VALUE(MID(A1,MATCH(TRUE,ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),0),SUM(--(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)))))

ctrl+shift+enter

then drag it down