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:
<colgroup><col></colgroup><tbody>
</tbody>
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.
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 |
<colgroup><col></colgroup><tbody>
</tbody>
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.