Scroll the menu to find all the places to visit
Excel - Failed Tabbed Data Paste
02 May 2019In my day job I work with a lot of raw data, and I make judicious use of Microsoft Excel to help me in the builds and transforms I need to accomplish. As many people are no doubt aware, Excel can be quite useful with the use of tab separated text as it treats each tab as a way to separate data out into cells and each new line as a new row in a spreadsheet. At least that’s what I thought. Recently however, I fell foul of finding tab separated data on my Windows clipboard not pasting into Excel in this way. But I did track down why this occurred.
Let’s start with an example of how things normally work. Let’s assume that I have the following data that I copy to the clipboard.
Fruit⇥Colour⇥Units
Apple⇥Red⇥3
Apple⇥Green⇥2
Banana⇥Yellow⇥6
Orange⇥Orange⇥2
When that gets pasted into Excel in cell A1 of a worksheet, I would typically expect the results to come out like so.
| A | B | C | |
|---|---|---|---|
| 1 | Fruit | Colour | Units |
| 2 | Apple | Red | 3 |
| 3 | Apple | Green | 2 |
| 4 | Banana | Yellow | 6 |
| s | Orange | Orange | 2 |
The effect I got when actually pasting the data however was rather different and the format came out with the data being compressed into one cell on each row.
| A | B | C | |
|---|---|---|---|
| 1 | FruitColourUnits | ||
| 2 | AppleRed3 | ||
| 3 | AppleGreen2 | ||
| 4 | BananaYellow6 | ||
| s | OrangeOrange2 |
All of the cell data was being compressed into single cells per row, and the tabs were effectively being stripped out. So what was going on?
Well the issue turned out to be related to some text processing I’d been carrying out earlier. Let’s start with a similar set of data, but let’s have it separated by commas rather then tabs.
Now there’s a handy feature in Excel that will let us transform that data and move the comma separated fields out into separate cells. It’s called Text to Columns, and can be found on the Data tab.
With the data s ected, and triggering this functionality, a wizard steps you through the process of converting the selection into columns. First we need to specify the text as delimited by commas (and yes, I know full well that it’s separated by commas and not delimited by them, but we’ll go with it).
The final step is simply to specify any data typing for formatting the data and then triggering the conversion.
The data is then converted to split across the cells.
Now lets immediately take a set of tabbed data to paste onto the next empty row.
When we paste it in, we find we have the issue described earlier.
As you may already have guessed, the source of the issue stems from the use of the comma separator in the Text to Columns functionality. It seems that when we specify a delimiter other than a tab, when we attempt subsequent pastes, the tabbing in tab separated data fields is ignored. The solution is therefore to go back in and specify the tab option. We can just go back into the Text to Columns wizard, set the tab option and then cancel out to do this.
Now if we re-paste the tabbed data on the clipboard to the next empty row, we get the nicely formatted data we would have originally expected.
While this all seems obvious now, it was painful tracking this down as hours had passed since I’d used the Text to Columns functionality. But hopefully if you’ve found this post because you’ve experienced a similar issue it’s saved you a little bit of time and consternation.
