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)


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

 

[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