May 18 2022 08:11 AM
Hi All,
I need help with this issue please ,
I have this column with ages written in format like for example ( 2 years , 1 year , 13 month , 8 weeks )
I want to convert all these data to reflect the Number of Months only for it to become something like (24 ,12,13,2 ) . you can find attached the column I'm trying to convert.
Thanks All.
May 18 2022 08:44 AM - edited May 18 2022 09:22 AM
Solution
in B2:
=LET(
delimP, FIND(" ",A2),
number, --LEFT(A2,delimP-1),
period, SUBSTITUTE(MID(A2,delimP+1,LEN(A2)-delimP),"s",""),
SWITCH(period,
"year", number*12,
"month", number,
"week", number/4,
"Unknown"
)
)
If you don't run Excel 2021 or 365:
=IFERROR(
VALUE(LEFT(A2,FIND(" ",A2)-1)) *
LOOKUP(
SUBSTITUTE(MID(A2,FIND(" ",A2)+1,LEN(A2)-FIND(" ",A2)),"s",""),
{"month","week","year"},
{1,0.25,12}
),
"Unknown"
)
May 18 2022 09:35 AM
An alternative could be a macro. In the attached file you can click the button in cell E2 to start the macro.
May 18 2022 01:30 PM
Practically the same with Beta functions
=LOOKUP(LEFT( TEXTAFTER( texts, " ") ), {"m","w","y"}, {1,0.25,12} )*TEXTBEFORE( texts, " ")
May 20 2022 04:42 AM
May 20 2022 05:52 AM
??? I don't understand. If you enter the formula in B2 and copy it down in the column you should get what you want. Anyway, see the attached file...
May 18 2022 08:44 AM - edited May 18 2022 09:22 AM
Solution
in B2:
=LET(
delimP, FIND(" ",A2),
number, --LEFT(A2,delimP-1),
period, SUBSTITUTE(MID(A2,delimP+1,LEN(A2)-delimP),"s",""),
SWITCH(period,
"year", number*12,
"month", number,
"week", number/4,
"Unknown"
)
)
If you don't run Excel 2021 or 365:
=IFERROR(
VALUE(LEFT(A2,FIND(" ",A2)-1)) *
LOOKUP(
SUBSTITUTE(MID(A2,FIND(" ",A2)+1,LEN(A2)-FIND(" ",A2)),"s",""),
{"month","week","year"},
{1,0.25,12}
),
"Unknown"
)