Oct 26 2022 04:14 AM
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:)
Oct 26 2022 04:48 AM
Solution=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.
Oct 26 2022 06:26 AM
Oct 26 2022 04:48 AM
Solution=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.