REGEX on Range of cells

[gallen]

Hello,

I'm not new VBA but fairly green on regex.

An example of the cells is:

1004006088 ProductName 3.0MM 2X20LM

I need to find the "MM" and then all numeric characters to the left including the period. However sometimes the text may have an space between the numerics and the "MM" so I need to allow for this too

1004006088 ProductName 3.0 MM 2X20LM

Ultimately I need to extract the 3.0MM or 3.0 MM ONLY.

Reading a few tutorials has just ended up me getting more confused.

Any help would be appreciated

 

[admiral100]

Hi,

If you want to have fun with a long formula

Ctrl+Shift+Enter NOT just Enter

=SUBSTITUTE(MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$10),1)),0),COUNT(1*MID(A2,ROW($1:$10),2))),RIGHT(MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$10),1)),0),COUNT(1*MID(A2,ROW($1:$10),2))),1),"")&" "&RIGHT(A2,(LEN(A2)-SEARCH("m*",A2,SEARCH("m*",A2,1)))-2)