Formula to extract numbers from text

[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?

 

[Don Guillett]

If it is always the same then just use the MID function
=mid(a1,5,5)

 

[Peo Sjoblom]

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

 

[Gord Dibben]

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

 

[JMay]

Thanks guys, I'm off and running....

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