Forum Discussion
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 never know how the application will split the rows into columns.. sometimes it delimits by tab, other times by spaces, and sometimes by something else (comma, pipe, etc).
I realize that Excel 'remembers' the setting used the for the most recent 'Text to Columns', but that's somewhat odd to me. Because if you want to change the delimiter setting from Space to Tab, you actually have to run through the Text to Columns process, even though you don't really need to use that function, and then paste the new data in.
Am I missing something? It would be nice if there was an easy way to configure the delimiter setting without all the clicks required to do Text To Columns.
4 Replies
- Awesome, I can create a couple versions of that so I can quickly enable different delimiters. Thanks!
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.
- 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.
