Separating/sorting numbers from text

[Ian1976]

Hi all,

I have a column (F1:F90) which will have either "service due in 9235mi" or "service overdue by 14755mi", is there a way of separating the 9235 or 14755 and put it in its own cell (K1:K90) the numbers will change daily.

Thanks
Ian

 

[Ian1976]

Copied it

=IF(ISNUMBER(SEARCH("due",F1)),"Service ","Overdue ")&TEXTAFTER(TEXTBEFORE(F1,"mi")," ",3)

 

[dreid1011]

Copied it

=IF(ISNUMBER(SEARCH("due",F1)),"Service ","Overdue ")&TEXTAFTER(TEXTBEFORE(F1,"mi")," ",3)

You may have an extra space in your F1 value. The formula is specifically looking for the 3rd space, if you have a 4th space before the number, it would explain the result you are getting.

 

[Ian1976]

Ah got it, thanks

 

[dreid1011]

Ah got it, thanks

Also, please see my original post, I had the formula wrong initially and you may have copied it before I corrected it.