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

New Contributor

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. 


Can anyone please help:)

2 Replies
best response confirmed by jonasv123 (New Contributor)


=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.


Thanks a lot:) worked perfectly