Extract information from cells

[Mantiyeah]

So I have a column with information like this:

XC-163 0827m Timber problems
0600m failed picture cv-76
ligts out 0987m p3
etc

What I need is to create a column only with the measurement info in it which goes behind the m, in this case:
0827
0600
0987

Any ideas of which functions I can use?

Thanks!

 

[sandy666]

maybe =RIGHT(MID(A1,1,SEARCH("m ",A1)-1),4)

 

[jtakw]

Hi,

Assuming what you want extracted is Always 4 character before "m", and in case it may be at the end of the text string:

Book1
	A	B
1	XC-163 0827m Timber problems	0827
2	0600m failed picture cv-76	0600
3	ligts out 0987m p3	0987
4	some other text 0123m	0123
Sheet309

Cell Formulas
Range	Formula
B1	=RIGHT(LEFT(A1,SEARCH("m ",A1&" ")-1),4)

 

[Scott Huish]

Those will have problems if any other text before the measurement ends in an m and a space

This UDF may help:

Function GetMeasurement(r As String) As String
Dim x As Long
For x = 1 To Len(r)
    If Mid(r, x, 5) Like "####m" Then GetMeasurement = Mid(r, x, 4): Exit Function
Next x
End Function

Excel 2010
	A	B
1	XC-163 0827m Timber problems	0827
2	0600m failed picture cv-76	0600
3	ligts out 0987m p3	0987
4	XC-163m 0827m Timber problems	0827
Sheet1

Cell Formulas
Range	Formula
B1	=GetMeasurement(A1)
B2	=GetMeasurement(A2)
B3	=GetMeasurement(A3)
B4	=GetMeasurement(A4)

 

[jtakw]

Those will have problems if any other text before the measurement ends in an m and a space

This will "narrow" it down a bit, but Not if there's another set of Numbers with an m behind it:

Book1
	A	B
1	XC-163 0827m Timber problems	0827
2	0600m failed picture cv-76	0600
3	ligts out 0987m p3	0987
4	some other text 0123m	0123
5	from now to 0750m and after	0750
Sheet309

Cell Formulas
Range	Formula
B1	=RIGHT(LEFT(A1,SEARCH("m ",A1&" ",MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)))-1),4)

 

[Scott Huish]

I'm glad you thought of a formula solution but how does my UDF differ from your formula results?

Excel 2010
	A	B	C
1	XC-163 0827m Timber problems 0425m	0827	0827
2	0600m failed picture cv-76	0600	0600
3	ligts out 0987m p3	0987	0987
4	some other text 0123m	0123	0123
5	from now to 0750m and after	0750	0750
Sheet1

Cell Formulas
Range	Formula
B1	=RIGHT(LEFT(A1,SEARCH("m ",A1&" ",MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)))-1),4)
B2	=RIGHT(LEFT(A2,SEARCH("m ",A2&" ",MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17)))-1),4)
B3	=RIGHT(LEFT(A3,SEARCH("m ",A3&" ",MIN(FIND({0,1,2,3,4}+{0;5},A3&1/17)))-1),4)
B4	=RIGHT(LEFT(A4,SEARCH("m ",A4&" ",MIN(FIND({0,1,2,3,4}+{0;5},A4&1/17)))-1),4)
B5	=RIGHT(LEFT(A5,SEARCH("m ",A5&" ",MIN(FIND({0,1,2,3,4}+{0;5},A5&1/17)))-1),4)
C1	=GetMeasurement(A1)
C2	=GetMeasurement(A2)
C3	=GetMeasurement(A3)
C4	=GetMeasurement(A4)
C5	=GetMeasurement(A5)

 

[Scott Huish]

What is the purpose of the 1/17?

EDIT: I think I figured it out. It's a decimal number that contains all the digits 0-9. Clever!

 

[admiral100]

And another way ....

=MID(A1,FIND("m ",A1)-4,4)

 

[Scott Huish]

Try that with this text:
from now to 0750m and after

 

[admiral100]

=MID(A1,SEARCH(" ????m "," "&A1&" "),4)


Try this "from 4m to 0750m and after" ....