Have you ever needed to combine data from multiple columns into a single column in Excel? Maybe you’ve got a name, address, and phone number spread across separate columns, but you need to join them into one column with each piece of information on its own line. Manually entering alt + enter a hundred times is not the way to go. In this post, I’ll show you how to automatically combine multiple columns into a vertical list in Excel using some simple formulas — it will save you tons of time and frustration.
Let’s dive in and walk through step-by-step how to use Excel’s CONCAT function and the CHAR code for a neater, more automated solution.
Prepping Your Data
First things first — before doing anything, let’s make sure your data is in the right format. Say you have a list of instructions in one long column that came over in a clunky format. You need this data in separate columns, and we’re going to use Text to Columns to organize it better. Here’s how:
- Highlight Your Data: Select the column where the data needs splitting. Use Ctrl + Shift + Down to quickly select the range.
- Open Text to Columns: Go to the Data tab on the ribbon and choose Text to Columns.
- Choose Your Delimiter: You can split the data by fixed width or by a special delimiter. If your data is separated by commas, spaces, or other characters, use the “Delimited” option. For everything else, you’ll likely want the “Fixed Width” option. In our case, we’ll go with “Fixed Width.”
- Set Split Points: Click where you want to split the data into new columns. Double-click any accidental dividers to remove them.
- Finish the Split: After previewing and confirming the split, hit Finish. Now, you should have your data divided into its own columns, making it easier to work with.
Once you’re done, you’ll have something like a “fruit,” “category,” and “quantity” in separate columns. Clean data makes the following steps much easier.
Combining Multiple Columns to a Vertical List in Excel
Now let’s move on to the real magic. You might have your data split into neat columns, but we want everything combined into one column and stacked vertically. This is where the CONCAT function (short for concatenate) comes in handy.
RELATED TOPIC: The 20 best Excel Tricks Everyone Should Be Using
Step-by-Step Instructions
Follow these simple steps to combine multiple columns into a single vertical list:
1. Use the CONCAT Function
In Excel, move into the first blank cell (say, G2 if your data starts from row 2). In that cell, enter the following formula:
=CONCAT(A2, CHAR(10), "-", B2, CHAR(10), "-", C2, CHAR(10), "-", D2)
Let’s break it down:
- A2, B2, C2, D2 represent the cells we’re pulling data from (like “Apples,” “211,” etc.).
- CHAR(10) inserts a line break (equivalent to hitting alt + enter).
- The “-“ adds a hyphen or whatever delimiter symbol you prefer. You can customize this with an asterisk, bullet point, or any character you want.
2. Apply Wrap Text
Before you hit Enter, make sure that wrap text is enabled. Without this, your lines will stay crammed together instead of stacking neatly. To turn it on, go to the Home tab and click Wrap Text under the Alignment group.
After you press Enter, you might be wondering why it’s not showing the stacked arrangement. It’s simply because the row height is too small. Quickly expand the row height by dragging the Excel row or using AutoFit to view the result.
Now you should see your content stacked in a vertical list with each data line properly separated!
RELATED TOPIC: How to Concatenate or Combine Data in Excel
3. Copy the Formula Down
Instead of writing the formula over and over, use the handle in the corner of the cell to drag it down. This will automatically apply the formula to the rest of your rows, populating the vertical list for each item.
Everything will be neatly pulled together from multiple columns into one, broken down into individual lines.
Important Tip: Removing the Formula for Future Use
If you plan to re-use or export this data somewhere else, you don’t want the cells filled with fragile formulas. Instead, convert the output into plain values:
- Select Your Data: Highlight all the cells where you used the CONCAT formula.
- Copy as Values: Right-click, choose Copy, then right-click again and choose Paste Special > Values. This will convert all the formulas into hard values.
- Format: Use the Format Painter from your original cells if necessary to keep the formatting consistent.
Why This Method is a Game-Changer
Using CONCAT and CHAR together is a lifesaver when you’re dealing with repetitive, tedious tasks involving lists. Why manually hit alt + enter when you can automate the entire process? By combining multiple columns into a single vertical list in Excel, you clean up your data and make it much easier to read.
Not only that, but this trick allows more flexibility by letting you choose the delimiters (i.e., what symbols separate your data points). It’s customizable according to your needs!
RELATED TOPIC: How to Use Text to Columns in Microsoft Excel
Conclusion
With just a bit of Excel formula magic, you can drastically streamline how you manage and combine multiple columns into a vertical list. Whether you’re working on a small dataset or a large one, using the CONCAT function and wrap text can save you tons of time. If this tutorial has been valuable to you, make sure to try this Excel trick in your daily routine. It will definitely make certain tasks much more efficient.
Feel free to leave a comment if you have any questions or if there are more Excel features you’d like to explore. And please subscribe to stay updated on future tutorials!
Thanks for stopping by and happy Excel-ing!
Get Started with Microsoft 365
Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.
This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.
