Formula to extract numbers from text

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

In my spreadsheet I have a Range A5:A10 which includes:

MLC/ 4169 Hburg CC's
MLC/ 4172 NMainCC's
MLC/ 4125 BlueR CC's
MLC/ 4128 BBurgCC's
MLC/ 4130 Frk CC's
MLC/ 4130 BMill CC's

In B5:B10 I'd like to enter a formula that
would produce:

4169
4172
4125
4128
4130
4130

Can this be done? if so, how?
 
Is it always four digits? If so you can use this

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1)),0)+
1,4)

array entered

otherwise you can use

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

also entered with ctrl + shift & enter
 
No formula here, but how about Data>Text to Columns.

Copy to Column B and use DTtC Space-Delimited and check to skip the columns
you don't want.

Gord Dibben XL2002
 
Thanks guys, I'm off and running....

Peo Sjoblom said:
Is it always four digits? If so you can use this

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1)),0)+
1,4)

array entered

otherwise you can use

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

also entered with ctrl + shift & enter



--

Regards,

Peo Sjoblom
Click to expand...
 
Back
Top