Forum Discussion
Solved
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...
=IFERROR(IF(SEARCH("secs",A2),VALUE(MID(A2,IFERROR(SEARCH("min ",A2)+4,1),2))),NA())
Hianthonyc89,
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))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 =IFERROR(IF(SEARCH("secs",A2),VALUE(MID(A2,IFERROR(SEARCH("min ",A2)+4,1),2))),NA())
