Excel keeps crashing when trying to convert dates using text to column

Occasional Contributor

I have a large excel file containing over 12,000 rows of data. I cannot filter the data by date because excel doesn't recognise the UK dates as a date format and it cant be changed using the change format settings. I know you can do it by using text to columns and use the DYM setting. However, when I try and do this with the current file it always crashes. How can I avoid this crash or is there an alternative method of resolving the issue?

12 Replies

@KIZZA94 Have you considered using Power Query (if you are not on a Mac, that is)? Would also be good to know exactly how the "dates" look like before you try to transform them into the UK format.


12000 rows is not dramatic for Text to Columns itself. Perhaps it crashes due to recalculation if a lot of dependencies. I'd try 1) do the same in safe mode 2) repair/update the Office.

It's true, in PQ, you could set up local datetime format.

@Sergei Baklan I tried both starting in safe mode as suggested and setting 'Calculation Options' to Manual but it still shuts down when I try convert them using text-to-columns. I can't repair or update office because I am using a work computer which is controlled by the organisation.


I am using a work computer so cannot download any software.
If only text-to-columns will cause shutdown, would you try insert a support column or add a support column at last with formular. Then filter the support column?
just a column ^O^
Even if you have the UK date, use fomular in another column to transfer it to the one you need. Then filter this column.
Maybe cannot solve your shutdown problem, but filter it first.

@KIZZA94 Power Query is integrated in Excel since 2016. So, no download needed if you are a recent Excel version.


- you may ask sysadmins to repair

- one more, copy/paste this column into new file and Text to Columns it. If works, that means the problem in formulas which reference this you column, not in Text to Columns itself.

best response confirmed by KIZZA94 (Occasional Contributor)

@Sergei Baklan  Brilliant, that second option worked! Thank you very much.