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.

 

[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?

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 L1	jam
pnp servo fault on hmi	on

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jtakw]

Hi,

You can also try this:

Book3.xlsx
	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	desiccant jam L1
10	pnp servo fault on hmi
Sheet808

Cell Formulas
Range	Formula
B2:B10	B2	=IFERROR(LEFT(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,"min"," min"))," ",REPT(" ",100)),200),100)+0,"")

 

[tatooedsn]

ty, I will give it a go!

 

[tatooedsn]

Thanks jtakw... that formula really works great!

 

[jtakw]

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?

 

[tatooedsn]

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]

Ok, great, thanks for the feedback.