SOLVED

Convert text to date

%3CLINGO-SUB%20id%3D%22lingo-sub-3394305%22%20slang%3D%22en-US%22%3EConvert%20text%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3394305%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3EI%20need%20help%20with%20this%20issue%20please%20%2C%3C%2FP%3E%3CP%3EI%20have%20this%20column%20with%20ages%20written%20in%20format%20like%20for%20example%26nbsp%3B%20(%202%20years%20%2C%201%20year%20%2C%2013%20month%20%2C%208%20weeks%20)%3C%2FP%3E%3CP%3EI%20want%20to%20convert%20all%20these%20data%20to%20reflect%20the%20Number%20of%20Months%20only%20for%20it%20to%20become%20something%20like%20(24%20%2C12%2C13%2C2%20)%20.%20you%20can%20find%20attached%20the%20column%20I'm%20trying%20to%20convert.%3C%2FP%3E%3CP%3EThanks%20All.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3394305%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3394579%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20text%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3394579%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1343788%22%20target%3D%22_blank%22%3E%40Abdellatif1995%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20259px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F372891i2A77CE64A2A51084%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EB2%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20%20%20delimP%2C%20FIND(%22%20%22%2CA2)%2C%0A%20%20%20%20number%2C%20--LEFT(A2%2CdelimP-1)%2C%0A%20%20%20%20period%2C%20SUBSTITUTE(MID(A2%2CdelimP%2B1%2CLEN(A2)-delimP)%2C%22s%22%2C%22%22)%2C%0A%20%20%20%20SWITCH(period%2C%0A%20%20%20%20%20%20%20%20%22year%22%2C%20%20number*12%2C%0A%20%20%20%20%20%20%20%20%22month%22%2C%20number%2C%0A%20%20%20%20%20%20%20%20%22week%22%2C%20%20number%2F4%2C%0A%20%20%20%20%20%20%20%20%22Unknown%22%0A%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIf%20you%20don't%20run%20Excel%202021%20or%20365%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20%20VALUE(LEFT(A2%2CFIND(%22%20%22%2CA2)-1))%20*%0A%20%20%20%20LOOKUP(%0A%20%20%20%20%20%20%20%20SUBSTITUTE(MID(A2%2CFIND(%22%20%22%2CA2)%2B1%2CLEN(A2)-FIND(%22%20%22%2CA2))%2C%22s%22%2C%22%22)%2C%0A%20%20%20%20%20%20%20%20%7B%22month%22%2C%22week%22%2C%22year%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B1%2C0.25%2C12%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%22Unknown%22%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3394815%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20text%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3394815%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1343788%22%20target%3D%22_blank%22%3E%40Abdellatif1995%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20alternative%20could%20be%20a%20macro.%20In%20the%20attached%20file%20you%20can%20click%20the%20button%20in%20cell%20E2%20to%20start%20the%20macro.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3396105%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20text%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3396105%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1343788%22%20target%3D%22_blank%22%3E%40Abdellatif1995%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPractically%20the%20same%20with%20Beta%20functions%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLOOKUP(LEFT(%20TEXTAFTER(%20texts%2C%20%22%20%22)%20)%2C%20%7B%22m%22%2C%22w%22%2C%22y%22%7D%2C%20%7B1%2C0.25%2C12%7D%20)*TEXTBEFORE(%20texts%2C%20%22%20%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3397653%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20text%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3397653%22%20slang%3D%22en-US%22%3EThank%20you%20for%20telling%20this%20excel%20formula...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3405073%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20text%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3405073%22%20slang%3D%22en-US%22%3Ethank%20you%20very%20much%20for%20this%20%2C%20but%20when%20i%20run%20this%20%2Cit%20only%20affects%20the%20first%207%20cells%20but%20not%20the%20rest%20.%20i%20have%20in%206735%20cell%20in%20the%20column%20i%20need%20to%20convert%20.%20I%20tried%20to%20Auto%20fill%20but%20it%20just%20copies%20the%20last%20still%20into%20the%20rest%20.how%20can%20this%20code%20be%20adjusted%20to%20affect%20all%20cells%20in%20the%20column%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3405336%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20text%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3405336%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1343788%22%20target%3D%22_blank%22%3E%40Abdellatif1995%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3F%3F%3F%20I%20don't%20understand.%20If%20you%20enter%20the%20formula%20in%20B2%20and%20copy%20it%20down%20in%20the%20column%20you%20should%20get%20what%20you%20want.%20Anyway%2C%20see%20the%20attached%20file...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

6 Replies
best response confirmed by Abdellatif1995 (Occasional Contributor)
Solution

Hi @Abdellatif1995 

 

_Screenshot.png

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

 

@Abdellatif1995 

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

@Abdellatif1995 

Practically the same with Beta functions

=LOOKUP(LEFT( TEXTAFTER( texts, " ") ), {"m","w","y"}, {1,0.25,12} )*TEXTBEFORE( texts, " ")
Thank you for telling this excel formula...
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?

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