SOLVED

# Extract Number If Text

Copper Contributor

# Extract Number If Text

I have data in the first column and I want a formula that will return the number before "min" in the 2nd column and the number before "secs" in the 3rd column.

 MIN SEC 35 secs #N/A 35 40 secs #N/A 40 1 min 5 secs 1 1 min 5 1 min 15 secs 1 1 min 15
9 Replies

# Re: Extract Number If Text

Hi@anthonyc89

see atached file.

MIN =

``=IFERROR(IF(SEARCH("min",A2)>0,VALUE(LEFT(A2,SEARCH("min",A2)-2))),NA())``

SEC =

``=VALUE(MID(A2,IFERROR(SEARCH("min ",A2)+4,1),2))``

# Re: Extract Number If Text

@dscheikey it works but if there is no "secs", its returning the "min" in the secs columns. See 2nd to the last row.

 MIN SEC 35 secs #N/A 35 40 secs #N/A 40 1 min 5 secs 1 5 1 min 15 secs 1 15 30 secs #N/A 30 1 min 20 secs 1 20 1 min 1 1 45 secs #N/A 45
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Extract Number If Text

``=IFERROR(IF(SEARCH("secs",A2),VALUE(MID(A2,IFERROR(SEARCH("min ",A2)+4,1),2))),NA())``

# Re: Extract Number If Text

Thank you @dscheikey . This worked!

# Re: Extract Number If Text

@anthonyc89  alternatively:

min:

``=IFERROR(--LEFT(A2,SEARCH("min",A2)-1),"")``

sec:

``=IFERROR(--MID(LEFT(A2,SEARCH("sec",A2)-1),IFERROR(SEARCH("min",A2)+4,1),99),"")``

# Re: Extract Number If Text

@anthonyc89 FILTERXML() may a good formula in this case.

``=MAX(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s"))``

# Re: Extract Number If Text

@dscheikey I get a "2" where 15 should read.

# Re: Extract Number If Text

It gives "2" because it can't find "min " (notice the space). change that to "min" instead but then you might have issues with the # characters so change the ,2 to ,3
OR try the FILTERXML formulas

# Re: Extract Number If Text

As variant, for modern Excel

``````=--TEXTBEFORE(A2, " min",,,,0)
=--LET( s, TEXTBEFORE(A2, " secs",,,,0), TEXTAFTER(  s, "min ",,,,s) )``````
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Extract Number If Text

``=IFERROR(IF(SEARCH("secs",A2),VALUE(MID(A2,IFERROR(SEARCH("min ",A2)+4,1),2))),NA())``