Clean Dates Exported From Text Field In Oracle Database

[wsnyder]

Hi all,

I ran a query in SQL Developer to get some dates from a database and exported to Excel.
The problem is the, the dates are stored in a text field and are in a wide variety of formats. Some samples:

• June 15th 2017
• 11/23/2016
• 5/16/17
• June 28th, 2017
• 4.28.2017

Anyone ever had to cleanup something like or any suggestions how to clean it up efficiently?
There are 6,646 rows.

Thanks
-w

 

[Dave Patton]

1. Determine if Oracle can export the data in a better format.

2. Use Data Text to Columns
3. Format the dates to your preference
4. Use Formula for the dates that did not convert

T202009b.xlsm
	A	B
2	June 15th 2017	Jun 15, 2017
3	Nov 23, 2016	Nov 23, 2016
4	May 16, 2017	May 16, 2017
5	June 28th, 2017	Jun 28, 2017
6	Apr 28, 2017	Apr 28, 2017
7
1e

Cell Formulas
Range	Formula
B2:B6	B2	=IF(ISNUMBER(A2),A2,DATE(RIGHT(A2,4),MONTH(1&LEFT(A2,3)),MID(A2,FIND(" ",A2)+1,2)))