Extract Numbers from a String of Text

[bobzy20]

Hi

I hope somebody can help me with this.

I’m looking for a way to extract a 7 digit number from a string of text, see my examples below. The number could be anywhere in the string.

Sanyo Yamaha II Slow Control Rod NEW 5841258 25984
Sanyo 4579542 Yamaha II Slow Controller NEW 12584
Sanyo Yamaha II Plastic Bag NEW 1248652 25489
Sanyo Yamaha II 9405566 Tool Bag NEW / 5894526
Sanyo Yamaha II/Super Car 1257945 Screw Bag C NEW 25689
Sanyo Yamaha Metal Parts 1584520 Bag NEW 56974

Thanks

Bob

 

[magpie2000k]

This seems to be answered in Post 4 here

https://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html

 

[István Hirsch]

Enter into B1 and try:

=MID(A1,FIND(" "&REPT("|",7)&" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&A1&" ","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|"),","," "),":"," "),";"," "),REPT("|",7)&". ",REPT("|",7)&" ")),7)

 

[bobzy20]

Thanks very much, worked perfectly.

Bob

 

[Peter_SSs]

Welcome to the MrExcel board!

IF your sample data is representative of your real data, then perhaps this might also suffice.

=AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:"&LEN(A1)-6)),7)+0,1)

 

[István Hirsch]

Thanks very much, worked perfectly.

Bob

You are welcome, thanks for the feedback.

 

[István Hirsch]

Welcome to the MrExcel board!

IF your sample data is representative of your real data, then perhaps this might also suffice.

=AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:"&LEN(A1)-6)),7)+0,1)

At me, 12345 is extracted from "er 12345 t". Is it my regional (number) settings?

 

[bobzy20]

Thanks very much, my real text if different to the sample text so didn't work correctly. The real strings of text have lots of different part numbers, some with 000 at the beginning which your code seems to ignore.

 

[Rick Rothstein]

...my real text if different to the sample text so didn't work correctly...

Why would you ever have thought it would?

 

[Peter_SSs]

At me, 12345 is extracted from "er 12345 t". Is it my regional (number) settings?

That is (partly) why I qualified my post with "IF the sample data was representative of the real data". All the sample data examples contained a 7-digit number surrounded by spaces & also no numbers greater than 7 digits. Your example is different to the OP's samples on the first point.

The real strings of text have lots of different part numbers, some with 000 at the beginning which your code seems to ignore.

In that case, does this work? If not, could you give a more representative set of sample data and the expected results?

=MID(AGGREGATE(14,6,(9&MID(A1,ROW(INDIRECT("1:"&LEN(A1)-6)),7))+0,1),2,7)