Convert Yrs Mths to Mths in Excel

[wsun]

Can someone pls help me with the formula or steps required to convert these values (xYrs, yMths) to Months in numerical format? For example "7 YEARS 1 MONTH" will be converted to "85". Thanks!

7 YEAR(S) 1 MONTH(S)

2 YEAR(S) 6 MONTH(S)

3 YEAR(S) 0 MONTH(S)

 

[Rick Rothstein]

My gut tells me there are probably better formulas than this, but this one works...

=12*REPLACE(A1,FIND(" ",A1),99,"")+REPLACE(LEFT(A1,LEN(A1)-9),1,FIND("R(",A1)+4,"")

 

[wsun]

You are a genius! It works, thanks!!!!

 

[Rick Rothstein]

As long as your years will never be greater than 99, here is a much shorter formula that will also work...

=12*LEFT(A1,2)+RIGHT(LEFT(A1,LEN(A1)-9),2)

 

[wsun]

Super, thanks so much!

 

[Tetra201]

Another approach -- with fewer function calls and reference calls:

=SUBSTITUTE(SUBSTITUTE(A1," YEAR(S)","")," MONTH(S)","/12")*12

 

[Rick Rothstein]

Looks like the same number of function calls as I used in Message #4 to me; but, yes, only one reference as compared to three that my formula had. And very clever creating a fraction text string so that multiplying by 12 will create an expression that Excel can evaluate. I would also note that, while I doubt it will matter, your formula is not bound by the 99 year limit that my formula is.