How to combine a list of data into one cell while putting a delimiter between each piece of data.

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

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:

```
```=A1

In the next cell, B2, input this formula:

```
```=B1 & "," & A2

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.

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:

```
```=B1 & "," & A2

Put a space between the numbers:

```
```=B1 & " " & A2

Put a dash between the numbers:

```
```=B1 & "-" & A2

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

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:

```
```=A1

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

```
```=B1 & " " & A2

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.

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:

```
```=B1 & " " & A2

Put a dash between the text:

```
```=B1 & "-" & A2

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

```
```=B1 & "; " & A2

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

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.

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.

Similar Content on TeachExcel

Generate a Unique List of Random Numbers With a Simple Formula

Tutorial: Sections: Versatile Formula to Generate a Unique List of Numbers Excel 365 Formula to Gen...

Tutorial: Sections: Versatile Formula to Generate a Unique List of Numbers Excel 365 Formula to Gen...

Require a Unique List of Numbers in a Range in Excel

Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...

Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...

Simple Excel Function to Combine Values in All Versions of Excel - UDF

: Excel function that combines values from multiple cells or inputs using a delimiter - work...

: Excel function that combines values from multiple cells or inputs using a delimiter - work...

PV Function - Get the Present Value in Excel

Tutorial: The Present Value (PV) function in Excel will return the current value of an investment.Â...

Tutorial: The Present Value (PV) function in Excel will return the current value of an investment.Â...

Loop through a Range of Cells in a UDF in Excel

Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...

Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...

FV Function - Get the Future Value in Excel

Tutorial: The Future Value function (FV) in Excel will return the future value of an investment ba...

Tutorial: The Future Value function (FV) in Excel will return the future value of an investment ba...