Excel: Extracting text

Hi Everyone, It took me awhile but I solved my issue. That said I wanted to know if there is a less convoluted formula to achieve the same result... I am trying to extract the number portion of a text string in order to convert it from text to a number which I will use to compare to the total useful life of that asset. I used the datedif() function to figure out the difference between two dates and the results of the formula were returned in the below format:

1 years 4 months
1 years 4 months
1 years 5 months
10 years 6 months
I recognized that the years would always be either the first or second value and the months would always be either the ninth or tenth value depending on how many years there were. To extract values I wanted I used the below formula: =VALUE(IF(MID(S8,3,5) = "years",LEFT(S8,1)& "." &MID(S8,9,2),IF(MID(S8,4,5) = "years",(LEFT(S8,2)& "." &MID(S8,10,2)),TRIM("."& MID(LEFT(S8,9),1,2))))) Is there a shorter formula I could use to achieve the same result? Thanks for the input.

This question generated 21 answers. To proceed to the answers, click here.

This thread is current as of September 16, 2014.

For more resources for Microsoft Excel