Aug 26 2022 12:50 PM
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 |
Aug 26 2022 01:16 PM
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))
Aug 26 2022 01:23 PM
@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 |
Aug 26 2022 01:39 PM
Solution=IFERROR(IF(SEARCH("secs",A2),VALUE(MID(A2,IFERROR(SEARCH("min ",A2)+4,1),2))),NA())
Aug 26 2022 02:00 PM
@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),"")
Aug 26 2022 07:43 PM
@anthonyc89 FILTERXML() may a good formula in this case.
=MAX(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s"))
Aug 30 2022 11:09 AM
Aug 30 2022 01:20 PM
As variant, for modern Excel
=--TEXTBEFORE(A2, " min",,,,0)
=--LET( s, TEXTBEFORE(A2, " secs",,,,0), TEXTAFTER( s, "min ",,,,s) )
Aug 26 2022 01:39 PM
Solution=IFERROR(IF(SEARCH("secs",A2),VALUE(MID(A2,IFERROR(SEARCH("min ",A2)+4,1),2))),NA())