using Find and splitting text

monsierexcel

New Member
Joined
Nov 19, 2018
Messages
29
i have the string "
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,


<colgroup><col width="571"></colgroup><tbody>
</tbody>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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))
 
Upvote 0
Another way
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(REPLACE(A4,1,FIND("mm",A4)-3,""),"mm","mm|",3),"|",REPT(" ",500)),400))
 
Upvote 0
Another way:

=TRIM(MID(SUBSTITUTE(A2," ", REPT(" ",250)),SEARCH("mm",SUBSTITUTE(A2," ", REPT(" ",250)))-50,250*5))
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
Diecut is removed by all three solutions


Excel 2013/2016
A
2Small Parcel Box Royal Mail Postal Box A4 310mm x 230mm x 150mm Diecut
3310mm x 230mm x 150mm
4310mm x 230mm x 150mm
5310mm x 230mm x 150mm
Sheet1
Cell Formulas
RangeFormula
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))
 
Upvote 0
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?
 
Upvote 0
Can you supply some samples of your strings.
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top