First, put your cursor into the cell in which you want the combined value, in our example we used F5. Next, put your cursor into the Formula Bar and type the equals sign, “=” Then, select the first cell you want to include which is A5 for this example, then type an ampersand sign “&” into the formula. Then, select the second cell you want to include, in our example it is B5 and follow it with an ampersand sign “&” as well. Repeat this process for all the columns that you would. I am working on creating a customer database, and need help combining multiple rows of information associated with the same person. The problem I am running into, is that I need to keep the data from each individual column separate as I merge them into the same row. This is the current layout of my excel sheet.

**In Excel, the CONCATENATE function allows you to combine text from different cells into one cell. In this tutorial, we’ll show you, step by step, how to use the CONCATENATE function.**

To combine text from multiple cells into one cell, use the & (ampersand) operator. Select the cell in which you want the combined data Type an = (equal sign) to start the formula Click on the first cell. When you work with Excel, you probably have to deal with data separated into multiple rows. For example name, city, age, etc. But sometimes you may want to combine cells from two different columns into a single column – for example, you have the First Name and Last Name columns and you want to merge them into a single Name.

Here's my entire playlist of Excel tutorials: Learn how to easily combine information from multiple cells in Excel and then put the.

When you’re analyzing data with numbers in Excel, it’s easy enough to combine or manipulate it through multiplication or addition. Manipulating text in Excel can be a little harder to achieve, however. So for cells that contain text, you’ll need to try something different.

That’s where the CONCATENATE function comes in. In this guide, we’ll cover:

So: How does the CONCATENATE function work? Let’s take a look.

If you want to combine data from two or more cells together, the best way to do it is to use the **CONCATENATE** function. This lets you combine multiple cells together, whether they contain numbers or text, without affecting the original cells. There are several reasons why you may wish to do this.

If you have a list of addresses, for instance, you could use CONCATENATE to combine each section of the address (the name, the first line, the town, etc) into a single cell. This could then be copied or used elsewhere.

From Excel 2016 onwards, **CONCATENATE** has been “replaced” with the **CONCAT** function. That means that, if you’re using a later version of Excel, you can use either CONCATENATE or CONCAT. Older versions of Excel will need to stick to CONCATENATE, however.

CONCAT works exactly the same way as CONCATENATE, so any references to CONCATENATE below will work exactly the same for CONCAT. Both are interchangeable, and while CONCATENATE remains in place for compatibility reasons, it could be removed in future Office releases.

There aren’t any additional benefits to using CONCAT over CONCATENATE, so feel free to use either function. If CONCATENATE is removed, however, then you’ll need to switch to using CONCAT instead.

The word concatenate is a technical term, but it simply means linking things together. That’s exactly what CONCATENATE and CONCAT do in Excel, combining the contents of different cells and displaying them in a new cell.

While you could copy and paste the contents of each cell into a single cell yourself, or use Excel’s cell merge feature to do it automatically, using CONCATENATE or CONCAT means that your original data is left untouched.

Most data analysts would prefer to leave source data intact, manipulating it instead by using pivot tables or other Excel functions. The advantage of CONCATENATE or CONCAT is that you don’t need to alter the source data at all, but instead refer to it. If the original data is modified, this will update the data displayed in the combined cell.

CONCATENATE also allows you to combine different types of data, such as numbers, dates, and text strings, which wouldn’t typically be possible without it.

While the CONCATENATE and CONCAT functions allow you to easily combine data from other sources, there are some limitations that you’ll need to be aware of.

These include, in no particular order:

- You can use a maximum of
**255 strings**or**8192 characters**in a CONCATENATE or CONCAT formula. - The formula must have at least
**one string**for it to work. - If you want to use a range of cells, you
**can’t use an array**(such as**A1:A10**). Each cell used in a CONCATENATE or CONCAT formula will have to be referenced manually (eg.**A1, A2, A3, A4,**etc.) - If you want to combine your data with special characters in a CONCATENATE or CONCAT formula, such as
**&**, then you’ll need to wrap them in**quotation marks**(**” & “**), otherwise Excel will attempt to treat them as part of the syntax of the formula itself. - If any cell references are wrong, or if an illegal character is used, Excel will return the
**#VALUE!**error message. - CONCATENATE and CONCAT will always
**return a text string**, even if the data you’re using is a number or is a cell with the number cell type. This can impact how it can be used by other functions. - Because CONCATENATE and CONCAT can only handle text strings, you can use it with other functions (such as
**TEXT**and**TODAY**) to correctly handle other data types, such as date numbers. - You can
**use the ampersand (&)**for basic concatenation without using either CONCATENATE or CONCAT functions (eg.**=A1&A2&A3**).

To use the CONCATENATE function in an Excel spreadsheet, here’s what you’ll need to do.

Start by opening your Excel spreadsheet and selecting an empty cell. Once you’ve selected the cell, click on the **formula bar** at the top to begin typing in your new CONCAT or CONCATENATE formula.

Once the formula bar is active, you can begin to type in your formula.

Both CONCATENATE and CONCAT have a very simple syntax that you’ll need to follow. For example:

**=CONCATENATE(text1, text 2, …)**

**=CONCAT(text1, text2, …)**

The reference to **text** (eg. **text1, text2**) in the example syntax above refers to a text string that can be input manually, or to a cell reference containing the data you wish to use. As we’ve mentioned, there’s a hard limit of **255 strings** that you can use here.

With your empty cell selected, begin to type your CONCATENATE or CONCAT formula by typing **=CONCATENATE(** or **=CONCAT(** in the formula bar.

You’ll then need to begin to select your data.

Both CONCATENATE and CONCAT can combine text strings from other cells (using cell references to identify them) or by using text strings within the formula itself. Each parameter (**text 1, text2,** etc) is then combined.

These follow the first open parentheses in sequence. For instance, **=CONCATENATE(A1,A2,A3,A4)** will merge the data from cells A1, A2, A3, and A4 together. Type this into the formula bar to create your formula, closing with a close parentheses, then hit the enter key to display the formula output.

You could also use the following CONCATENATE or CONCAT examples where cell references or text strings are used together:

**=CONCATENATE(A1, B1, C1)**

**=CONCATENATE(“This is a text string”, “ which will be combined together”, “ to make a long sentence”)**

**=CONCAT(A1, B1, C1, “ which is very long”)**

As the examples above show, any text strings that you add manually will need to be wrapped in quotation marks. This is to ensure that the contents of a text string in your formula aren’t considered to be arguments in the formula itself.

Because CONCATENATE and CONCAT combine numbers, text, and other types of data together, the only way to do this is to treat the output as a text string. This means that, even if the values are numbers, the formula output will be considered as text, and you won’t be able to manipulate the number type.

Adding certain special characters to a CONCATENATE or CONCAT formula, such as ampersands and hyphens, can break it. To avoid this, you’ll need to make sure that special characters are wrapped in quotation marks.

To do this, open your Excel spreadsheet, select an empty cell, and use the formula bar to type a CONCATENATE or CONCAT formula similar to the following examples, replacing the cell references or text strings with your own data:

**=CONCATENATE(A1, “ & “, A2)**

**=CONCAT(“This is a text string”, “ & “, “it is long”)**

You may want to do this to make the output from a CONCATENATE or CONCAT formula easier to follow. You’ll also want to do this to avoid words from text strings merging together, such as **“This is a text string&it is too long”,** rather than **“This is a text string & it is too long”**.

When you use CONCATENATE or CONCAT in Excel, the output is displayed as a text string. This can sometimes malform numbers with different number types, such as dates. To avoid this, you can use the **TEXT** function to ensure that the date number is converted by CONCATENATE or CONCAT and displayed correctly.

To do this, you could use the following formula, replacing the date values with your own:

**=CONCATENATE(“The date today is “, TEXT(TODAY(), “dd/mm/yyyy”))**

**=CONCAT(“Jim’s birthday is on the “,TEXT(“10/11/2020”,”dd/mm/yy”))**

By using TEXT with CONCATENATE or CONCAT, you can ensure that the number will be displayed in the date format that you require.

The CONCATENATE and CONCAT function, used in a formula, will output on a single line. This isn’t ideal for every scenario, especially if you’re trying to combine certain types of data, such as a postal address.

To get around this limitation, you can use CONCATENATE or CONCAT with the **CHAR** function, which will be able to output the ASCII code for a line break (10). To do this, you’ll need to use the following formula, replacing the cell references with your own:

**=CONCATENATE(A1, CHAR(10), C1)**

**=CONCAT(A1, CHAR(10), B1)**

Previously, you’d need to use **CHAR(13)** if you were using Excel on Mac for this to work. However, in recently released versions of Excel on Mac, you’ll be able to use **CHAR(10)** like Windows users.

This will also work for other types of special characters, such as slashes or ampersands, but most users will find it’s easier to wrap these types of characters in quotation marks instead.

The CONCATENATE function in Excel is considered an essential tool for data analysts to have in their arsenal, allowing you to quickly combine different types of data. It isn’t the only way you can do this, however, as the newer CONCAT function offers the same functionality in the latest versions of Excel. If you’re keen to continue exploring, why not check out the XLOOKUP function next, or the AVERAGE function? Alternatively, here’s a round-up of 10 Excel formulas that every data analyst should know.

If you’re new to data analytics, you can try a free introductory data analytics short course. And, for more tutorials and resources, check out the following:

Here this tutorial provides some formulas to join multiple cells into one cell with comma as separator as below screenshot shown, and also explains how the formulas work in Excel.

**Generic formula:**

text_1&','&text_2&','&…&',' &text_n |

**Arguments**

Text_1, text_2, text_n: the cells or text strings you want to combine with comma. |

**How this formula work**

For instance, you want to combine the cells of each row across column B, column C, column D and column E into one cell with commas, please use below formula in a blank cell.

=B3&','&C3&','&D3&','&E3 |

Press **Enter** key, then the cells are combined with comma separated.

**Explanation**

& : using this connector to combine cells and commas.

**Remark**

If you want to directly use texts instead of cell references in the formula, please embrace the text strings with double quotation marks.

**Generic formula:**

CONCATENATE(text_1,',',text_2,',',…',',text_n) |

**Arguments**

Text_1, text_2, text_n: the cells or text strings you want to combine with comma. |

**How this formula work**

For instance, you want to combine the cell B3, C3, D3 and E3 into one cell with commas, please use below formula in a blank cell.

=CONCATENATE(B3,',',C3,',',D3,',',E3) |

Press **Enter** key, then the cells are combined with comma separated.

**Explanation**

CONCATENATE function: CONCATENATE function is used to join text together.

**Remark**

If you want to directly use texts instead of cell references in the formula, please enclose the text strings with double quotation marks.

If there are blank cells which are used to be combined, there will be extra comma in the combined cell by using above 2 formulas (& and CONCATENATE) as below screenshot shown.

In this case, please choose formula 3.

**Generic formula:**

=SUBSTITUTE(TRIM(text_1&' '&text_2&' '&…&' '&text_n),' ',', ') |

**Arguments**

Text_1, text_2, text_n: the cells or text strings you want to combine with comma. |

**How this formula work**

For instance, you want to combine the cell B3, C3, D3 and E3 into one cell with commas, please use below formula in a blank cell.

=SUBSTITUTE(TRIM(B3&' '&C3&' '&D3&' '&E3),' ',', ') |

Press **Enter** key, then drag the fill handle down to apply this formula to the cells you need to combine, then the cells are combined with comma separated.

**Explanation**

TRIM function: the TRIM function removes all extra spaces from the text string. Here *TRIM(B3&' '&C3&' '&D3&' '&E3)* will combine cells with spaces and then clear the extra spaces, this formula will stop the trailing or leading comma.

SUBSTITUTE function: the SUBSTITUTE function is used to replace text with the specific text. Here* =SUBSTITUTE(TRIM(B3&' '&C3&' '&D3&' '&E3),' ',', ')* will replace space with comma and space in the combined text string (the combination of B3, C3, D3 and E3).

**Remark**

If you want to directly use texts instead of cell references in the formula, please enclose text strings with double quotation marks.

**Combine cells with other separators**

If you want to use other separators to replace commas, change “,” in the formulas to other separators. For instance, combine with plus symbol.

*=SUBSTITUTE(TRIM(B6&' '&C6&' '&D6&' '&E6),' ','+')*

Or*=CONCATENATE(B3,'+',C3,'+',D3,'+',E3)*

Or*=B3&'+'&C3&'+'&D3&'+'&E3*

- Check if a cell contains a specific text

To check if a cell contains some texts in range A but does not contain the texts in range B, you can use an array formula which combines the COUNT, SEARCH and AND function in Excel - Check if a cell contains one of several values but exclude other values

This tutorial will provide a formula to quickly handle the task that check if a cell contains one of things but excluding other values in Excel and explain the arguments of the formula. - Check if cell contains one of things

Supposing in Excel, there is a list of values in column E, you want to check if the cells in column B contain all of the values in column E, and return TRUE or FALSE. - Check if cell contains number

Sometimes, you may want to check if a cell contains numeric characters. This tutorial provides a formula which will return TRUE if the cell contains number, FALSE if cell does not contain number.

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

- Designed for 1500 work scenarios, helps you solve 80% Excel problems.
- Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
- Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
- 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.

Loading comment... The comment will be refreshed after 00:00.

Coments are closed