How to convert multiple text values to a date format using a single formula

How to convert multiple text values to a date format using a single formula

Assuming there is an uncleaned Date format in Column A, some of the cells values are entered as Text but there is no synchronicity and dates are mentioned in various formats.

Convert multiple text values to a date format

You have a large dataset but you don’t want to apply multiple formulas to clean the Date format neither you want to play with Text to Columns, then what is the solution?

Here is the solution:

Apply this formula in cell B2 then copy the formula until the last row and problem solved!

=IF(ISTEXT(A2),DATEVALUE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,","," "),"."," "),"  "," "))," ","-")),A2)

#Tip – extend the SUBSTITUTE formula if still there are multiple special characters.  

COMMENTS (1)

  • comment-avatar

    I saw a lot of website but I conceive this one contains something extra in it in it