May 18 2022 11:50 AM
May 18 2022 12:07 PM
SolutionSometimes, Excel is too clever for its own good.
Select an empty cell.
On the Data tab of the ribbon, click Text to Columns.
Select Delimited, then click Next >.
Clear the check boxes of all delimiters.
Click Finish.
The problem should be gone (until the next time...)
Jun 24 2022 10:38 AM
Oct 04 2022 10:31 AM
Is there any way to turn it off for good? Or will I need to do this every time I've used Text to Columns?
Oct 04 2022 11:04 AM
Excel will remember the settings during a session, but when you start Excel, there shouldn't be presets.
Oct 04 2022 11:08 AM
I always do the splitting a file thing first, then other comparisons later in the next process. But this way of turning it off, while awkward, works without me having to exit Excel and re-enter.
Oct 04 2022 11:15 AM
The next to last reply in VBA code to reset after execution or to end command contains a macro that will reset Text to Columns.
If you copy that macro to a module in your Personal Macro Workbook PERSONAL.XLSB and assign it to a keyboard shortcut and/or Quick Access Toolbar button, you can reset it at any moment using one keystroke or mouse click.
Oct 04 2022 11:23 AM
Oct 04 2022 12:07 PM
Jun 09 2023 03:37 PM
@Hans Vogelaar Thanks so much for your answer! This has been a frustration for me for years! FYI, it would not let me even open the Text to Columns dialog with a blank cell selected, but it worked fine with a populated cell selected. Thanks so much!
Apr 12 2024 07:14 PM
Your solution started to work(!thanks) but I no longer can remove unwanted delimiting bars. As soon as I select 'space' as a delimiter it separates first and last names into separate columns.
Apr 12 2024 11:46 PM
As soon as I select 'space' as a delimiter it separates first and last names into separate columns.
That is to be expected. If you select Space as delimiter, all spaces will act to start a new column.
Apr 15 2024 06:42 AM
It doesn't have to be space, you can set up whatever delimiter you want, just set it to something that isn't going to come up in your data. "|" is popular, or "\" or "%".
Apr 15 2024 09:56 AM
@Hans Vogelaar
you don't need to click FINISH.... you can just press ESC (it feels weird but it works)
May 18 2022 12:07 PM
SolutionSometimes, Excel is too clever for its own good.
Select an empty cell.
On the Data tab of the ribbon, click Text to Columns.
Select Delimited, then click Next >.
Clear the check boxes of all delimiters.
Click Finish.
The problem should be gone (until the next time...)