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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,897
Office Version
  1. 365
Platform
  1. Windows
Will you only have one number in the cell?
Also what version of Excel are you using & is Windows or Mac?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

tatooedsn

New Member
Joined
Jun 13, 2016
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Will you only have one number in the cell?
Also what version of Excel are you using & is Windows or Mac?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
No, there will be other numbers. My next project is to find numbers in that same comment box after the letters STA. So each comment box will have a different station number and time. So it may look like "Mod 4 on STA3 had to be rebooted and took 5MINS". I'm having to use mins as a search reference since I found out words like co"min"g also have min and mess up the formula lol.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,897
Office Version
  1. 365
Platform
  1. Windows
In that case please answer my other questions & post half a dozen samples of the sort of text you get.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

tatooedsn

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

ADVERTISEMENT

In that case please answer my other questions & post half a dozen samples of the sort of text you get.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
My apologies. I will get my profile updated asap. I have 365 Enterprise 2008 version on a Windows 10 machine. Unfortunately my employer has disabled installation of software. Here are some examples:
IssueDuration
mod4 STA10 rollers cleaned at shift change 10mins
mod1 STAdesiccant/ na/ Abox 3 kept coming up with desiccant errors, cleaned blade/ 5mins
mod20 STA2 cleaned rollers duting foil change 15mins
Desiccant 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. / 30min
sta100 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 120mins
mod50 STAdesiccant box 3 keeps saying piece too long, even after new dessiccant rolls have been added. cleaned rollers feeding into knife/ 5mins
mod120 abox 3 had cutter replaced. 25 min
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,897
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, one further question, do you have the LET function?
 

tatooedsn

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

ADVERTISEMENT

I have a LEN, but no LET. I did find a formula in this forum that will work as long as there are no spaces between the # and minutes. There is a section that I probably don't need, but I'm not able to follow the logic in these formulas with the multiple parentheses.
=TRIM(RIGHT(SUBSTITUTE(LEFT(F209,FIND("mins",F209)-1)," ",REPT(" ",99)),99))&MID(F209,FIND("mins",F209),FIND(" ",F209&" ",FIND("mins",F209))-FIND("mins",F209))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,897
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff 1.xlsm
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
9
Data
Cell Formulas
RangeFormula
B2:B8B2=INDEX(FILTERXML("<l><m>"&SUBSTITUTE(TRIM(SUBSTITUTE(A2,"min"," min"))," ","</m><m>")&"</m></l>","//m"),LEN(TRIM(SUBSTITUTE(A2,"min"," min")))-LEN(SUBSTITUTE(A2," ","")))
 

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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,897
Office Version
  1. 365
Platform
  1. Windows
No that's part of the formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,190
Messages
5,640,741
Members
417,165
Latest member
Hilders1

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