How to Transpose Single-Cell Lists in Excel to Columns

By actually using my head to think instead of as a suitable desk-banging object, I've finally solved a data-reformatting problem that has plagued me for years: how to switch a single-cell list into a column of individual cells. In other words, how to make cell A1 in the above Excel screen shot look like range A3:A7 in the below Excel screen shot without re-keying it all.

The solution is a very easy find/replace, super-quick conversion to HTML, and then a copy/paste from a web browser back into Excel. 

Check it! (Just click the images to see them bigger. I had to make them small to fit into this Blogger template.)

Step 1: Copy the single-cell list (A1, in this case). Paste it into a notepad-like program. I prefer Notepad ++.

Step 2: Open the find/replace dialog and find ";" and replace it with HTML paragraph openers and closers, like this:

Finding and replacing all the semi-colons and replacing them with HTML paragraph tags will make the list look like this:

Step 3: Finish up the HTML code. Since the phrase "ice cream" did not have a semicolon preceding it in the original Excel list, we need to manually add a paragraph opener before "ice cream". Likewise, the word "cupcakes" had no semicolon after it so we need to add a paragraph closer after it. Then we need to add the html opener tag before the list and the html closer tag after the list. Like this:

Step 4: Save as a text file with the file extension ".htm" or ".html".

Step 5: Open that HTML file in any web browser and it will display, TA DA!, as a list in a column! Why? Well, we basically converted all the semicolons (or commas or whatever character was dividing up the list items in cell A1) into paragraph marks. Then we save the list as an HTML file. A web browser reads HTML code just like a human reading a book -- on a single line from left to right -- but renders paragraphs in a column. Basically we told the web browser to do the reformatting for us! Score!

Step 6: Copy and paste the list from your web browser into Excel, thank your browser for its hard work, then go take a coffee break -- you just saved yourself so much data-entry time that you deserve it!

Obviously, the longer the list, the more time you'll save. Short examples like above are easy to re-key quickly. I just used it on a 128-item list of email addresses that an author had simply copy/pasted into an email. Pesky authors! Why does it always fall to the publisher to handle the formatting?

You can combine the "text-to-columns" function with this technique to handle more complex lists. For example, if the list were person and title and separated by commas and semicolons like this:

You would find/replace the semicolons as above. Once converted to HTML, this would render the list like this:

 

You would copy/paste this into Excel, then highlight these cells and convert "text-to-columns" using commas as the delimiter. Voila!

Naturally, the only situation for which this technique wouldn't work is if the list items were divided using only commas, like: Dave T., CEO, Erin T. CFO, Jon H., Lead Engineer, etc. Then find/replace has no way of knowing which commas you really want to convert to paragraph marks.