SOLVED

# Convert text to date

Occasional Contributor

# Convert text to date

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

# Re: Convert text to date

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

# Re: Convert text to date

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

# Re: Convert text to date

Practically the same with Beta functions

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

# Re: Convert text to date

Thank you for telling this excel formula...

# Re: Convert text to date

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?

# Re: Convert text to date

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