What Does it Mean to Use Concatenate in Excel [+ Why It Matters]

Copy and paste shortcuts come in handy until you have hundreds of data points that you can combine and reformat manually.

Fortunately, you can use Excel’s JOIN feature to save time (and contain the carpal tunnel) merging information from cells, rows, or columns.

Means “merge” or “combine”, concatenation is a way of combining the contents of two or more cells into one cell.

The feature allows you to combine data from columns, cells, ranges, and rows into any format, making it easy to quickly connect names and addresses, or to display dates and times correctly.

There are several ways to set up the CONCATENATE formula in Excel, so we’ll go through the steps required to use this feature and achieve your worksheet goals.

How to concatenate in Excel

Although there are several ways to combine text in Excel worksheets, let’s focus on the COMBINE function.

It combines the contents of two or more cells in one cell without physically changing the shape of the cell and is often used to join pieces of text (called Text strings or Strings) from single cells to one cell. The resulting text string is the combination of all the strings in your CONCATENATE formula.

Here’s how to concatenate in Excel:

Suppose you have a list of customers with separate first and last names. You will need everyone’s full names to create a retargeting campaign. So you want to combine the text from column A (first name) with the text from column B (last name).

Before using the CONCATENATE function, you need to create a new column for your combined text. In the example below it is column C (full name).

source

Now you can concatenate the first and last name. To do this, you need to understand both the syntax of the function and the formatting of the text strings that make up the formula.

Concatenate Excel formula

Like all Excel functions, the CONCATENATE formula begins with the equal sign (=), followed by the function name, an open bracket, and the text arguments. No need to heat up – in this context, arguments simply tell the formula which cells to combine.

= CONCATENATE (text1, [text2], …)

= CONCAT (text1, [text2], …)

Text 1: This is the first argument to be connected and can be a number, text or a cell reference.

text2, text3 etc .: These are the additional elements that can be combined. The CONCATENATE formula can connect up to 255 elements with a total of 8,192 characters.

Note: In all versions of Excel 2016 and beyond, the CONCATENATE function has been replaced by the CONCATENATE function. This function works the same way, with the additional possibility of combining text over a range of cells (ie = CONCAT (A2: D8)). Although you can still access the CONCATENATE feature for compatibility reasons, Microsoft warns that CONCATENATE may not be available in future versions of Excel.

Concatenate Excel with spaces

Double quotation marks (“”) contain a space between text arguments wherever one should appear. Make sure to put a comma before and after each set of quotation marks, otherwise an error message will appear and the formula will not run. You know your formula is correct when you hit Enter and the new text string looks exactly how you want it.

In the name example, you want to combine the text in cells A16 and B16, so add these arguments to the formula. Make sure to place the arguments in the order you want them to appear. To get “First Name Last Name” set up the CONCATENATE formula as follows:

= CHAIN ​​((B16, “”, A16)

Note: If at least one of the arguments of the CONCATENATE function is invalid, the formula returns a #VALUE! Error.

source

Now is the time to apply the CONCATENATE formula to each name in the list. Simply move the mouse pointer over the combined cell until a plus sign (+) appears in the lower right corner. Then click and hold the cursor as you drag the cursor down into column C and highlight all of the cells you want to merge.

source

When you let go of the cursor, voila! The formula is applied to each cell. This avoids having to type the formula for each cell by allowing you to concatenate dozens of text strings in a snap.

Concatenate Excel strings

Regardless of whether you combine text and numbers, the result of the CONCATENATE function is always a text string. The above naming example is a pretty simple example, but it is possible to create longer, more descriptive text strings in Excel. The key to doing this is to make sure that your results have value for everyone who will use the information.

For example, let’s say you’re working on an email campaign and want to personalize the subject line with each customer’s name. Instead of entering name for name manually, you can combine the text strings with the JOIN function. For this example, I’ve used a memorable email subject line from Warby Parker.

source

I write the following formula, making sure to put commas and spaces where they should appear.

= CONCATENATE (B2, “,”, A2, “,”, “”, C2)

source

Then I apply the formula to the entire column to generate my list of personalized subject lines.

source

When experimenting with longer text strings, note that each cell reference must be listed separately in the CONCATENATE function, as it cannot recognize arrays. For example, your formula should look like this = CONCATENATE (B1, B2, B3, B4) rather than = CONCATENATE (B1: B4).

Combining text is easy enough, but adding dates and times into the mix can lead to a messy formula and erroneous results.

Concatenate the date and time in Excel

You can avoid time and date problems by embedding the TEXT function in the Excel COMBINE formula. This gives you control over formatting when combining text with a number or date.

Let’s see how you can combine all of this information. In this worksheet, I want to record the date and time of each blog post so I can refer to the information in my monthly performance analysis.

source

The formula required to join these three strings may look complicated, but once you’ve decided how to format the date and time, it’s simple. I want to leave the date and time as they are, so I’ll add the date (MM / DD / YYYY). and time formatting (hh: mm: ss) in the CONCATENATE formula.

= CONCATENATE (A2, TEXT (B2, “mm / dd / yyyy”), “at”, “”, TEXT (C3, “hh: mm: ss”))

The result is a text string that provides meaningful context by splitting the date and time the posts were published.

source

Apply the function to the rest of the worksheet by following the same steps as before. Drag the plus sign (+) in the lower right corner of the combined cell down the column and highlight the cells you want to concatenate.

source

Excel chaining area

When you are excited about your new skills in Excel, you may find yourself in the urge to organize and organize worksheets with thousands of data points. Using the CONCATENATE function can speed up your work, but know that there are limitations.

In Excel, you can only combine 255 elements with a total length of 8,192 characters using the JOIN function. So you have to work in sections if you want to create hundreds (or thousands) of new strings of text.

The CONCATENATE function doesn’t accept arrays (A3: E5) so make sure you list each argument you want to include in the formula. When you need to combine a huge range of tens or hundreds of cells, try one of these keyboard shortcuts.

Use the CONTROL button.

1. Enter the first part of your formula = CONCATENATE (
2. Hold down the Ctrl key and select the individual cells that you want to combine.
3. Release the CTRL key, type a closing bracket, and press ENTER.

Use the TRANSPOSE function.

When you need to combine hundreds of cells, you don’t want to waste time clicking each cell. Instead, use the TRANSPOSE function to create an array before swapping it with each cell.

1. Select the cell where you want to use the CONNECT function.
2. Enter the TRANSPOSE formula to generate an array of cells. It looks similar: = TRANSPOSE (A1: A10)
3. With the TRANSPOSE cell selected, press the F9 key on the formula bar to replace the array with the individual values ​​to be concatenated.
4. Delete the brackets around the values ​​so that only a list remains.
5. Enter the CONCATENATE formula in front of the values ​​and close the formula with parentheses.
6. Press Enter to see your new text string.

While some Excel users say the CONCATENATE feature is deprecated, it is still a useful shortcut for combining strings of text without affecting the rest of your worksheet. If you’re new to the program, take the time to learn about using Excel and see these helpful keyboard shortcuts. You’ll find your way around worksheets and be more efficient in no time.