Forum Discussion
Solved
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.
Can anyone please help:)
=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.
2 Replies
=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
