String pick up depending of certain values

[ABC3132]

Hello.
I have the following question:

Original	Caliber (mm)
Apple 10 mm	10​
12 mm Red Apple	12​
Green 15 mm Apple	15​

I would like to find a function where when the string "mm" is found I can pick te previous numbers up.
Any clue?
Thanks

 

[JamesCanale]

The other guys do it better than me.

MrExcelPlayground20.xlsx
	A	B
2	Apple 10 mm	10
3	12 mm Red Apple	12
4	Green 15 mm Apple	15
5	The big green 14.5 mm apple	14.5
Sheet25

Cell Formulas
Range	Formula
B2:B5	B2	=VALUE(MID(LEFT(A2,SEARCH(" mm",A2)-1),MAX(IFERROR(FIND(" ",LEFT(A2,SEARCH(" mm",A2)-1),ROW(INDIRECT("1:"&(LEN(LEFT(A2,SEARCH(" mm",A2)-1)))))),1)),99))

 

[Coyotex3]

Another option based on your example. James formula might be more robust than this one.

IF(TEXTAFTER(TEXTBEFORE(A1,"mm")," ")="",TEXTBEFORE(A1,"mm"),TEXTAFTER(TEXTBEFORE(A1,"mm")," "))

 

[etaf]

FYI - those text functions I think are not in 2019 version of excel , which is in profile - I think its only 365 version
BUT that is based on 2 websites i use/check

Excel Functions 365 New Functions

Excel Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

Excel functions (alphabetical) - Microsoft Support

A complete list of all Excel functions in alphabetical order.

support.microsoft.com

 

[ABC3132]

The other guys do it better than me.

MrExcelPlayground20.xlsx
	A	B
2	Apple 10 mm	10
3	12 mm Red Apple	12
4	Green 15 mm Apple	15
5	The big green 14.5 mm apple	14.5
Sheet25

Cell Formulas
Range	Formula
B2:B5	B2	=VALUE(MID(LEFT(A2,SEARCH(" mm",A2)-1),MAX(IFERROR(FIND(" ",LEFT(A2,SEARCH(" mm",A2)-1),ROW(INDIRECT("1:"&(LEN(LEFT(A2,SEARCH(" mm",A2)-1)))))),1)),99))

wow. Thanks JamesCanale!

 

[ABC3132]

Another option based on your example. James formula might be more robust than this one.

IF(TEXTAFTER(TEXTBEFORE(A1,"mm")," ")="",TEXTBEFORE(A1,"mm"),TEXTAFTER(TEXTBEFORE(A1,"mm")," "))

Unfortunately my Excel is corporative so the VBA are capped. Thanks anyway Coyotex3

 

[etaf]

FYI - thats not VBA
its just a formula , using functions that exist in 365 version
just used the code tags on the forum