Extract string in between space and character

[tatooedsn]

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.

 

[Fluff]

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]

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]

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]

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:

Issue	Duration
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]

Thanks for that, one further question, do you have the LET function?

 

[tatooedsn]

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]

Ok, how about

+Fluff 1.xlsm
	A	B
1
2	mod4 STA10 rollers cleaned at shift change 10mins	10
3	mod1 STAdesiccant/ na/ Abox 3 kept coming up with desiccant errors, cleaned blade/ 5mins	5
4	mod20 STA2 cleaned rollers duting foil change 15mins	15
5	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	30
6	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	120
7	mod50 STAdesiccant box 3 keeps saying piece too long, even after new dessiccant rolls have been added. cleaned rollers feeding into knife/ 5mins	5
8	mod120 abox 3 had cutter replaced. 25 min	25
9
Data

Cell Formulas
Range	Formula
B2:B8	B2	=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]

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]

No that's part of the formula.