Search code examples
exceldateexcel-formulatext-extraction

Extract separated date from a text

so I have this text in excel: Wed Aug 04 00:00:00 WIB 2021 and I need to extract the date to the cell beside it like 04-Aug-21 which is for me kind of complicated, can anyone help?

so I already can extract one by one

=RIGHT(A1, 4)
=MID(A1, 5, 3)
=MID(A1, 9, 2)

but when I combine using this formula it become error

=DATE(VALUE(RIGHT(A1, 4)), MONTH(1 & MID(A1, 5, 3)), VALUE(MID(A1, 9, 2)))
Solution

  • You almost had it right, using the formula you have come up with:

    =DATE(MID(A3, 25, 4), MONTH(1&LEFT(MID(A3, 5, 3), 3)), MID(A3, 9, 2))

    Alternative approach:

    =DATEVALUE(MID(A2,9,3) & MID(A2,5,4) & RIGHT(A2,4))

    enter image description here