Date Formatting

[sp3124]

I have a data in which data is represented in the following format :

1st Aug 2021 | 8:57 pm

I want to change this into 08/01/2021 8:57:00.

I have tried using text to columns, custom data format but no success. Can someone provide any inputs on how I can achieve this ?

Thank you.

[#2 · Accepted solution]

@sp3124 

If it's OK to use a formula to return a date/time value in another cell:

With a text value in A2, enter the following formula in B2:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"st",""),"nd",""),"rd",""),"th",""),"|","")

Format B2 with your preferred date/time format, then fill down.

If you want to convert the value in the cell itself, it would require VBA.

[#3]

@Hans Vogelaar
This worked :)
I am curious to know how is this working in the background ? Using substitute, I am able to remove the st,nd etc in my cell but how am I able to convert it into date format ? I was not able to do it before.

[#4]

@sp3124 

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"st",""),"nd",""),"rd",""),"th",""),"|","")

removes the vertical line character "|" and the suffixes "st" etc.

In your example, this results in the text string "1 Aug 2021  8:57 pm". While it is still text, Excel can recognize it as date+time.

Applying - forces Excel to treat the string as a date+time, but it turns it negative. The second - turns it positive again.

In general, applying -- is a convenient way to convert a string that represents a date or number to a "real" date or number.

[#5]

@Hans Vogelaar Understood.
Thank you so much for all the help!