Convert text to date

[Abdellatif1995]

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.

[#2 · Accepted solution]

Hi @Abdellatif1995 

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"
)

[#3]

@Abdellatif1995 

An alternative could be a macro. In the attached file you can click the button in cell E2 to start the macro.

[#4]

@Abdellatif1995 

Practically the same with Beta functions

=LOOKUP(LEFT( TEXTAFTER( texts, " ") ), {"m","w","y"}, {1,0.25,12} )*TEXTBEFORE( texts, " ")

[#5]

thank you very much for this , but when i run this ,it only affects the first 7 cells but not the rest . i have in 6735 cell in the column i need to convert . I tried to Auto fill but it just copies the last still into the rest .how can this code be adjusted to affect all cells in the column?

[#6]

@Abdellatif1995 

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