Excel not consistent when pasting external data as it pertains to column separators

I do a lot of ETL work, and often use Excel as a tool to profile and manipulate data coming from a variety of sources during development and for prod support. When pasting data into Excel, you nev...

excel

HansVogelaar
Aug 07, 2023
Wooderson

See this post for a macro that you can run to reset the 'Text to Columns' settings.

If you store the macro in your personal macro workbook PERSONAL.XLSB, you can call it whenever you need. You can assign it to a Quick Access Toolbar button and/or custom keyboard shortcut for ease of use.

Riny_van_Eekelen

Platinum Contributor

Aug 07, 2023

Wooderson I would suggest you have a look into Power Query and forget about Text-to-columns if you want some serious ETL within excel.

https://exceloffthegrid.com/power-query-introduction/

Wooderson
Copper Contributor
Aug 07, 2023
I appreciate it, I have used power query, but my use-case is quick looks at log files, rejected record files, sample data files, text files people send, and the like.

I have a variety of other enterprise tools for ETL.

Forum Discussion

Excel not consistent when pasting external data as it pertains to column separators

Resources