Search code examples
excelstringdateexcel-formulastring-operations

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

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.

Solution

  • If your data is in A1:

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