using Find and splitting text

[monsierexcel]

i have the string "

<colgroup><col width="571"></colgroup><tbody>
[TD="width: 571"]Small Parcel Box Royal Mail Postal Box A4 310mm x 230mm x 150mm Diecut"

I want to pull the text "310mm x 230mm x 150mm" i can use Find and Right to pull the first mm and extract specifc level of characters but im not sure how to pull the whole string so the first space before 310mm until the last mm after 150?

Hope someone is clever with cutting these out in formula!

thank you all,

[/TD]

</tbody>

 

[Fluff]

There's probably a simpler way, but how about
=TRIM(MID(A4,FIND("mm",A4)-3,FIND("mm",A4,FIND("mm",A4,FIND("mm",A4)+1)+1)-FIND("mm",A4)+5))

 

[Fluff]

Another way
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(REPLACE(A4,1,FIND("mm",A4)-3,""),"mm","mm|",3),"|",REPT(" ",500)),400))

 

[DanteAmor]

Another way:

=TRIM(MID(SUBSTITUTE(A2," ", REPT(" ",250)),SEARCH("mm",SUBSTITUTE(A2," ", REPT(" ",250)))-50,250*5))

 

[Fluff]

Just realised that my 2nd option is wrong, but Dante's works regardless of the size of the box & is therefore better than either of mine.

 

[monsierexcel]

thank you both - however this keeps the word diecut in the thread - i want to cut this so it just shows "310mm x 230mm x 150mm"

 

[Fluff]

Diecut is removed by all three solutions

Excel 2013/2016
	A
2	Small Parcel Box Royal Mail Postal Box A4 310mm x 230mm x 150mm Diecut
3	310mm x 230mm x 150mm
4	310mm x 230mm x 150mm
5	310mm x 230mm x 150mm
Sheet1

Cell Formulas
Range	Formula
A3	=TRIM(MID(A2,FIND("mm",A2)-3,FIND("mm",A2,FIND("mm",A2,FIND("mm",A2)+1)+1)-FIND("mm",A2)+5))
A4	=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,1,FIND("mm",A2)-4,""),"mm","mm|",3),"|",REPT(" ",500)),400))
A5	=TRIM(MID(SUBSTITUTE(A2," ", REPT(" ",250)),SEARCH("mm",SUBSTITUTE(A2," ", REPT(" ",250)))-50,250*5))

 

[monsierexcel]

thanks guys - what if the string was different and i just wanted to pull a specific section of content between the first xxmm and the last xxmm?

 

[Fluff]

Can you supply some samples of your strings.

 

[monsierexcel]

Hi Fluff so any string where there are "mm" at the beginning of the string this could have 1,2 or 3 preceeding numbers in and at the end.

"240mm x 20 mm"
"this product is 240mm x 20mm x 40mm"
"40mm x 20mm x 80mm"
"400mm x 8 mm"