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

 

[Joe4]

As long as all the records follow one of those two structures, this formula should work:

=TEXTBEFORE(TEXTAFTER(F1," ",3),"mi",1)

 

[Ian1976]

Hi Joe4,thats works a treat, don't suppose you could explain the logic?

 

[Scott Huish]

Hi Joe4,thats works a treat, don't suppose you could explain the logic?

I'm not Joe4, but it's taking the text after the 3rd space, and then the text before mi

 

[Ian1976]

Thanks Scott!

 

[Scott Huish]

Although if it's always the last thing in the phrase, this should also work:
=--TEXTBEFORE(TEXTAFTER(A2," ",-1),"mi")

I added the -- before it to coerce it to an actual number, you could do this with Joe4's formula too.

 

[Ian1976]

Would the formula be able to give me depending on the cell containing either "service due in 9235mi" or "service overdue by 14755mi "Service 1234" or "Overdue 1234" as a result or is that a whole new formula?

Thanks

 

[dreid1011]

If the strings are only ever those two options:

Book1
	F	G
1	service due in 9235mi	Service 9235
2	service overdue by 14755mi	Overdue 14755
Sheet1

Cell Formulas
Range	Formula
G1:G2	G1	=IF(ISNUMBER(SEARCH("overdue",F1)),"Overdue ","Service ")&TEXTAFTER(TEXTBEFORE(F1,"mi")," ",3)

 

[Ian1976]

Thanks dreid, not sure if its something I've done but the result for G1 comes back as Service in 9235?

 

[dreid1011]

Thanks dreid, not sure if its something I've done but the result for G1 comes back as Service in 9235?

Did you copy the formula or retype it? Can you post exactly what formula you have?