How to convert string "yyyy mmm ABC" to "yyyymm" date format using Excel formula?

Question

Suppose, in Excel, I have cell value equal to

"2020 Aug ABC"

I want to convert this to 202008 date format using an excel formula because I would want to use the output value, that is, 202008 to perform some operation.

score 2 · Accepted Answer · answered Mar 12 '21 at 07:54

2

If your data is in A1:

=LEFT(A1,4) & TEXT((DATEVALUE(MID(A1,6,3) & " 1")),"mm")

answered Mar 12 '21 at 07:54

ZygD

22,092
39
79
102

1

Too similar for an answer... `=LEFT(A1,4)&TEXT(MONTH(1&MID(A1,6,3)),"00")` – Robin Mackenzie Mar 12 '21 at 07:56
thanks for your answer, may I know what is mm at the end refer to? – royalewithcheese Mar 12 '21 at 08:05
Actually it is Date format, which only shows month in two symbols (`08` instead of `8`) – ZygD Mar 12 '21 at 08:15

score 1 · Answer 2 · answered Mar 12 '21 at 07:54

1

Import "2020 Aug ABC" in cell A1
Select A1 - Go to Data tab - Data Tools Area - Press Text to Columns
Select Delimited - Press Next - Select Space - Press Next
Press Finish
In D1 import =A1&RIGHT("0" & MONTH(DATEVALUE(B1&"1")),2)

Output:

answered Mar 12 '21 at 07:54

Error 1004

7,877
3
23
46

score 1 · Answer 3 · answered Mar 13 '21 at 00:04

1

Or just using :

=TEXT(MID(A1,6,3)&LEFT(A1,4),"yyymm")

answered Mar 13 '21 at 00:04

bosco_yip

3,762
2
5
10

score 0 · Answer 4 · answered Mar 12 '21 at 07:56

0

A1 = 2020 Aug ABC

B1

=LOOKUP(9^9,LEFT(A1,4)/1%+FIND(TEXT(ROW($1:$12)*28,"mmm"),A1)^0*ROW($1:$8))

answered Mar 12 '21 at 07:56

Can.U

461
2
8

What is the `9^9` bit doing? – Robin Mackenzie Mar 12 '21 at 08:39
A big number, shortening. – Can.U Mar 12 '21 at 08:58

score 0 · Answer 5 · answered Mar 12 '21 at 08:15

0

Just another approach if your data is always same pattern.

=TEXT(DATEVALUE("01-"&MID(A1,6,3)&"-"&LEFT(A1,4)),"yyyymm")

answered Mar 12 '21 at 08:15

Harun24hr

30,391
4
21
36

How to convert string "yyyy mmm ABC" to "yyyymm" date format using Excel formula?

5 Answers5