A colleague of mine reached out to me the other week with a question; is it possible to convert a text value such as Product 1, Product 2, Product 3…..etc. to column values in Excel?
So from something like this
To something like this:
My answer was of course yes! We can do just about anything with data in Excel.
Now clearly in the above example it wouldn’t take long to cut and paste or retype the 5 values into column format; but imagine if the list of values was 1000 values or more long (say goodbye to your evening and probably your sanity).
Thankfully Excel gives us two tools for achieving this quickly and easily
- Text to Columns
- Paste Special > Transpose
To achieve the above result quickly and easily here’s what we do:
- Highlight the column with the comma separated values in it, in this case highlight column A
- Go to the Excel Data ribbon and click the Text to Columns icon
- Select the Delimited option
- Now click Next >
- Ensure the Comma option only is ticked
- Click Next >
- If your values are text values and you want to use them as text values then select the Text option; select Date for date values and for all other values (including numbers) select the General option. I’ve selected Text
- Click Finish
- After re-sizing column A, I now have this
- If at the beginning I wanted the comma separated values in cell A1 to be shown as column headings then I could stop here; but instead I want these values to be listed vertically as row values in column F. To achieve this I need to continue
- Highlight and copy cells A1 to E1
- Now for the clever bit! We want to paste the values we’ve just copied into cell F1, but in a vertical column format rather than a horizontal row format. To do this go to the Excel Home ribbon and select Paste Special from the Paste icon drop down menu.
- Leave the Paste and Operation settings as they are, unless you see a need to change them, and tick the Transpose box
- Click OK
- You should now see the values you copied shown in a horizontal column format in column F
And that’s it!
Clearly in this example we didn’t save much time but we could have saved hours if the list of comma separated values was significantly longer or if we had multiple comma separated lists to work with.