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

 

[bobzy20]

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.


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)

Thanks, I will try and let you know.

Bob

 

[bobzy20]

Works perfectly, thanks very much.

How many years have you been using Excel as all I really use is VLOOKUP!

Bob

 

[Peter_SSs]

Works perfectly, thanks very much.

Good news! You are very welcome.

How many years have you been using Excel as all I really use is VLOOKUP!

I can't recall - must be well over 20 years now. Hang around this forum a bit and you'll soon lean about some more functions and techniques.

 

[bobzy20]

Thanks, I will.

Thanks everyone for your help.

Bob