Forum Discussion
Solved
Date formatting Excel
Hello all,
Have been trying to get text formatted as dates correctly in Excel. Alas, nothing happens. Source is as follows: https://www.investing.com/commodities/crude-oil-historical-data
Despite trying to remove commas, spaces and the like, the string of text does not want to move an inch.
Any ideas? Ideally, I'm after dd-mmm-yyyy format.
Many thanks,
Kirk
KirkExcel Copied the table and pasted as text into Excel. Then used Text to Columns on the data ribbon.
Selected the date column.
Step 1: Delimited, next
Step 2: Un-check all delimiters, next
Step 3: Select Date and MDY from the dropdown, Finish
Then it looks like in the attached file. Column A now has real dates.
KirkExcel Copied the table and pasted as text into Excel. Then used Text to Columns on the data ribbon.
Selected the date column.
Step 1: Delimited, next
Step 2: Un-check all delimiters, next
Step 3: Select Date and MDY from the dropdown, Finish
Then it looks like in the attached file. Column A now has real dates.
- Thank you! This works!
KirkExcel Perhaps you want to look into Power Query. Then you don't have to bother with TTC in the future. Nothing difficult. All can be done by clicking in the user interface. Example attached.
Hi KirkExcel
No idea how you get the information (copy/paste, Power Query, something else...) from the webiste you referenced. Values in column A below are copied/pasted from that site
in B2:
=DATE( --RIGHT(A2,4), MATCH(LEFT(A2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0), --MID(A2,5,2) )then format the cell as you want
