SOLVED

converting text of number of years and months into months (as number)

Copper Contributor

converting text of number of years and months into months (as number)

So I have a large dataset with a column being "number of years and months remaining)". The values are given in text format for example (20 years 3 months) or (19 years). I would now like to convert this into total number of months which would be formatted as a number.

2 Replies
best response confirmed by jonasv123 (Copper Contributor)
Solution

Re: converting text of number of years and months into months (as number)

``=IFERROR(NUMBERVALUE(LEFT(A1,FIND(" years",A1)-1)),0)*12+IF(COUNT(SEARCH("years",A1))=0,NUMBERVALUE(LEFT(A1,FIND(" months",A1)-1)),IFERROR(NUMBERVALUE(MID(A1,FIND("years ",A1)+6,2)),0))``

You can try this formula.

Re: converting text of number of years and months into months (as number)

Thanks a lot:) worked perfectly
1 best response

Accepted Solutions
best response confirmed by jonasv123 (Copper Contributor)
Solution

Re: converting text of number of years and months into months (as number)

``=IFERROR(NUMBERVALUE(LEFT(A1,FIND(" years",A1)-1)),0)*12+IF(COUNT(SEARCH("years",A1))=0,NUMBERVALUE(LEFT(A1,FIND(" months",A1)-1)),IFERROR(NUMBERVALUE(MID(A1,FIND("years ",A1)+6,2)),0))``

You can try this formula.