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!

 

[Scott Huish]

I don't think you're quite getting my point. You have to kind of isolate 4 numbers and an m specifically, otherwise there will be problems. The other formula suggested would fail here too. My UDF however still returns the correct value:

Excel 2010
	A	B	C	D
1	This is a dream 4m to 0750m and after	drea	am 4	0750
Sheet1

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

 

[admiral100]

Finally understood... and yet I wanted to solve the problem by formula rather than VBA
I think I've done

Ctrl+Shift+Enter NOT just Enter

B1 =MID(A1,MAX(1,MIN(IFERROR(SEARCH(" "&TEXT(--MID(A1,ROW($1:$55),4),"0000")&"m"," "&A1),9^9))),4)

	A	B
1	XC-163 1227m Timber problems 0425m	1227
2	0600m failed picture cv-76	0600
3	ligts out 0987m p3	0987
4	some other text 0123m	0123
5	from 3em now to 3752m and after	3752
6	frohm 3453m 4m to 0750m and after	3453
7	from 5m now to 1750m and after	1750
8	r 0523m 4m to 0750m and after	0523
9	XC-163 1227m Timber problems 0425m	1227
10	XC-163 237m Timber problems 0425m	0425
11	XC-163 234527m Timber problems 0425m	0425
12	XC-163 dream 34527m Timber problems 0425m	0425
13	This is a dream 4m to 0750m and after	0750
14	1600m failed picture cv-76	1600

<tbody>
</tbody>

 

[lrobbo314]

Yet another UDF version...

Function NumX(S As String) As String
Dim RX As Object: Set RX = CreateObject("VBScript.RegExp")
Dim Pattern As String: Pattern = "(\d{4})(m)"


With RX
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
    .Pattern = Pattern
    Set MX = .Execute(S)
End With


NumX = MX(0).submatches(0)
        
End Function