Sometimes you just can't get Excel to do what you want. Given a CSV file and some careful Visual mode blockwise editing, Vim is actually pretty good for editing columns of values.
I had columns of URLs that were separated by tabs that I wanted to edit with Visual-block mode, but I also wanted to wrap each line in quotes and brackets to make a JSON document. This is what the file looked like:
When dealing with structured data with varying column lengths, I like to pad the whitespace so the columns line up. Then I strip out the whitespace at the end. To do this in Vim, expand the tab size:
This changes the number of spaces a tab stands for. Now that everything appears to line up, convert the tabs to spaces:
Retab replaces tabs with strings of spaces.
At this point I made my edits and tweaks, with Normal mode and Visual-block. In the end I wanted to collapse the large amount of white space into a single space, so I did this:
`%s/[^ ]\zs \+/ /g`
I've found myself using
retab quite often when I've got CSV data that Excel just won't process the way I want.