Extract string in between space and character

tatooedsn

New Member
Joined
Jun 13, 2016
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi All. I'm trying to extract a number of minutes in a string. So for example if I have a comment in a cell that says "washed dirty clothes and it took 45 mins". I just want to extract the 45 to another column by searching by "mins". I'm trying to avoid using text to columns to do this. Any help or suggestions appreciated.
 

tatooedsn

New Member
Joined
Jun 13, 2016
Messages
25
Office Version
  1. 365
Platform
  1. Windows
I'll give it a shot when I get a moment :) Is the filterxml command just for it to show up on the message board?
It works great until someone forgets to put the min or a space between the word before the digit. I can live with this.. tyty.
desiccant jam L1jam
pnp servo fault on hmion
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,692
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

You can also try this:

Book3.xlsx
AB
1
2mod4 STA10 rollers cleaned at shift change 10mins10
3mod1 STAdesiccant/ na/ Abox 3 kept coming up with desiccant errors, cleaned blade/ 5mins5
4mod20 STA2 cleaned rollers duting foil change 15mins15
5Desiccant Feeder #1 / NA / Feeder was giving chute jam errors with no jam present. when reviewing further the blade was hanging up. removed the blade assembly and did not see any abnormal ware on the blade . cleaned the blade and reassembled. worked with Ron C. / 30min30
6sta100 card jam on lane 2, caused about half of the flights on top to come out of place. had to reinsert and check for correct orientation 120mins120
7mod50 STAdesiccant box 3 keeps saying piece too long, even after new dessiccant rolls have been added. cleaned rollers feeding into knife/ 5mins5
8mod120 abox 3 had cutter replaced. 25 min25
9desiccant jam L1 
10pnp servo fault on hmi 
Sheet808
Cell Formulas
RangeFormula
B2:B10B2=IFERROR(LEFT(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,"min"," min"))," ",REPT(" ",100)),200),100)+0,"")
 

tatooedsn

New Member
Joined
Jun 13, 2016
Messages
25
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks jtakw... that formula really works great!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.

Curious, see my Post #13, A9, do you want the number 1 returned as result even if there's no "min" or "mins" behind?
 
Solution

tatooedsn

New Member
Joined
Jun 13, 2016
Messages
25
Office Version
  1. 365
Platform
  1. Windows
You're welcome, thanks for the feedback.

Curious, see my Post #13, A9, do you want the number 1 returned as result even if there's no "min" or "mins" behind?
I'd rather have it blank, so it works out.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Ok, great, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,839
Messages
5,638,652
Members
417,040
Latest member
EC1728

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
Top