Quickly Combine a List of Values and Put a Delimiter Between Each Value in Excel

This tutorial uses a variation of concatenation in order to include delimiters (separators) between each value, which allows for more useful and readable data.

This tip will save you hours!

(make sure to download the accompanying workbook so you can follow the tutorial)

Sections:

Combine List of Numbers and Put a Comma, or whatever you want, Between each Number

Combine List of Text and Put a Space, or whatever you want, Between Each Word

Combine List of Numbers and Put a Comma, or whatever you want, Between each Number

Let's take a list of numbers and put them all in one cell with a comma between each number.

Here is our setup:

In cell B1, the first cell next to the top of the list, input this formula:

In the next cell, B2, input this formula:

Copy the formula from B2 down the entire list; select cell B2 and double-click the bottom right corner of it or just click and hold the bottom right corner and move it down the cells with your mouse.

Here is the result:

Now, select the last cell of the list in column B, cell B10, and hit Ctrl + C to copy it.

Go to the cell where you want this list to be and click it.  Then hit Alt + E + S + V and Enter and this will paste the visible values from B10 into your new cell instead of the formula from cell B10.

Delete the data in column B and you are done.

This may seem like a lot of steps but, once you know how to do it, you can do it in just a few seconds.

Use Different Delimiters

It is really easy to use something other than a comma to separate the values, just replace the comma in the formula below with whatever you want.

Original formula:

Put a space between the numbers:

Put a dash between the numbers:

As you can see, it is quite simple to change the delimiter.

Combine List of Text and Put a Space, or whatever you want, Between Each Word

Let's combine text into one cell and put a space between each word so it is easier to read and understand.

(if you followed the above section on doing this with numbers you will notice that this one is almost exactly the same)

Here is what we have:

In cell B1, the first cell next to the top of the list, input this formula:

In the next cell, B2, input this formula (this is what will actually combine the text):

Copy the formula from B2 down the entire list; select cell B2 and double-click the bottom right corner of it or just click and hold the bottom right corner and move it down the cells with your mouse.

It will look like this:

Now, select the last cell of the list in column B, cell B5, and hit Ctrl + C to copy it.

Go to the cell where you want this list to be and click that cell.  Hit Alt + E + S + V and Enter and this will paste the values from B5 into your new cell instead of the formula that is in cell B5.

Delete the data in column B and you have a nice clean result.

This may seem confusing at first but, once you get the hang of doing this, it will only take a few seconds to do it, regardless of how large the list is.

Use Different Delimiters

It is really easy to use something other than a space to separate the text in the new cell, just replace the space in the formula below with whatever you want.

Original formula:

Put a dash between the text:

Put a semi-colon with a space after it between the text:

As you can see, it is quite simple to put whatever you want between the text values.

Potential Issues and their Solution

When you deal with data like this, you are often importing it from somewhere and have little control on what kinds of things are added to or removed from your data.  Make sure to look through our data analysis tutorials for Excel to figure out how to solve any issues you might have with this.

One quick tip is to trim the original data before you try to combine it into one list.  Use the TRIM() function in Excel and that will make sure there are no leading or trailing spaces that might interfere with how you want the new list to appear.

Notes

This tutorial merely shows you the method of combining data into one list with a delimiter between each piece of data.  Don't forget to also clean the data and do whatever you need to it before it is combined into one list, especially if the data is imported from another place or piece of software.

An easy way to do this, though for more advanced users, is to simply nest all of the data cleaning functions and formulas within the formulas shown in this tutorial; that way, you can do it all in one step, though that could get confusing depending on what you are doing.

Don't forget to download the accompanying workbook.