Copy and paste shortcuts are handy until you have hundreds of data points to manually combine and reformat.
Fortunately, you can use the CONCATENATE Excel function to save time (and curb carpal tunnel) when joining information from cells, rows, or columns.
Meaning "to join together" or "to combine," concatenate is a way to unite the contents of two or more cells into one cell.
The function allows you to combine data from columns, cells, ranges, and rows into whatever format you desire, making it easy to quickly join names and addresses or properly display dates and times.
There are various ways to set up the CONCATENATE formula in Excel, so we'll walk through the steps required to use this function and achieve your worksheet goals.
How to Concatenate in Excel
While there are several ways to combine text in Excel worksheets, we'll be focusing on the CONCATENATE function.
It combines the contents of two or more cells into 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 individual cells into one cell. The resulting text string is the combination of all strings in your CONCATENATE formula.
Here's a look at how to concatenate in Excel:
Let's say you have a list of customers whose first and last names are separate. You need everyone's full names to build a retargeting campaign, so you want to join text from column A (First Name) with the text from column B (Last Name).
Before using the CONCATENATE function, you have to create a new column for your combined text. In the example below, it's column C (Full Name).
Now, you're ready to concatenate the first and last names. To do that, you need to understand both the syntax of the function and how to format the text strings that make up the formula.
Excel Concatenate Formula
Like all Excel functions, the CONCATENATE formula starts with the equals sign (=), followed by the function name, an open parenthesis, and the text arguments. No need to get heated – in this context, arguments simply tell the formula what cells to combine.
=CONCATENATE(text1, [text2], ...)
=CONCAT(text1, [text2], ...)
text1: This is the first argument to join and can be a number, text, or cell reference.
text2, text3, etc: These are the additional items to combine. The CONCATENATE formula can join up to 255 items, with a total of 8,192 characters.
Note: In all versions of Excel 2016 and beyond, the CONCATENATE function has been replaced with the CONCAT function. This function works in the exact same way, with the added ability to combine text over a range of cells (i.e., =CONCAT(A2:D8)). While you can still access the CONCATENATE function for compatibility reasons, Microsoft warns that CONCATENATE may not be available in future versions of Excel.
Excel Concatenate with Space
Double quotations (" ") include a space between the text arguments wherever you want one to appear. Just remember to insert a comma before and after each set of quotations or else an error message will pop up and the formula won't run. You'll know your formula is correct if you hit "Enter" and the new text string appears exactly how you want.
In the name example, you want to combine the text in cells A16 and B16, so add those 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 like this:
=CONCATENATE((B16, " ", A16)
Note: If at least one of the CONCATENATE function's arguments is invalid, the formula will return a #VALUE! Error.
Now it's time to apply the CONCATENATE formula to every name in the list. Simply hover over the combined cell until a plus sign (+) appears in the bottom right-hand corner. Then click and hold while dragging the cursor down column C, highlighting all of the cells you want to join.
When you release the cursor, voila! The formula is applied to each cell. This prevents you from having to type out the formula for each cell by letting you concatenate dozens of text strings in an instant.
Excel Concatenate Strings
Whether you combine text and numbers, the result of the CONCATENATE function is always a text string. The naming example above is a fairly simple example, but it's possible to create longer, more meaningful text strings in Excel. The key to doing so is to ensure your results provide value to whoever is using the information.
Let's say you're working on an email campaign and want to personalize the subject line with each customer's name. Instead of manually typing out name after name, you can use the CONCATENATE function to combine the text strings. For this example, I used a catchy email subject line from Warby Parker.
I write the following formula, making sure to include commas and spaces where I want them to appear.
=CONCATENATE(B2," ,", A2, ","," ", C2)
I then apply the formula to the entire column to generate my list of personalized subject lines.
As you experiment with longer text strings, know that every cell reference in the CONCATENATE function must be listed separately because it can't recognize arrays. For instance, your formula should look like =CONCATENATE (B1, B2, B3, B4) rather than =CONCATENATE(B1:B4).
Combining text is simple enough, but throwing dates and times into the mix can result in a messy formula and error-ridden results.
Concatenate Date and Time in Excel
You can prevent issues with time and dates by embedding the TEXT function into the CONCATENATE Excel formula. This will let you control the formatting when combining text with a number or a date.
Let's look at how to combine all of this information. In this worksheet, I want to record the date and time each blog post went live so I can reference the information during my monthly performance analysis.
The formula needed to join these three strings may look complicated, but it's simple once you decide how to format the date and time. I want to leave the dates and times as they are, so I include the date (mm/dd/yyyy) and time (hh:mm:ss) formatting into 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 sharing what date and time the posts went live.
Apply the function to the rest of the worksheet using the same steps as before. Drag the plus (+) sign in the bottom right-hand corner of the combined cell down the column, highlighting the cells you want to concatenate.
Excel Concatenate Range
If you're excited about your new Excel skills, you may have the urge to jump in and organize worksheets with thousands of data points. Using the CONCATENATE function can speed up your work, but know that there are limitations.
Excel only allows you to combine 255 items, up to a total of 8,192 characters, using the CONCATENATE function. So you have to work in sections if you want to create hundreds (or thousands) of new text strings.
The CONCATENATE function doesn't accept arrays (A3:E5), so make sure to list out each argument you want to include in the formula. If you need to combine a massive range of tens or hundreds of cells, try out one of these shortcuts.
Use the CONTROL key.
- Type in the first part of your formula =CONCATENATE(
- Hold down the CONTROL key and select the individual cells you want to combine.
- Release the CONTROL key, type a closing parenthesis, and hit ENTER.
Use the TRANSPOSE function.
If you need to combine hundreds of cells, you don't want to waste time clicking on each cell. Instead, use the TRANSPOSE function to create an array before swapping it out with the individual cells.
- Select the cell you want to use the CONCATENATE function.
- Type in the TRANSPOSE formula to generate an array of cells. It will look similar to this: =TRANSPOSE(A1:A10)
- With the TRANSPOSE cell selected, press the F9 key in the formula bar to replace the array with the individual values to be concatenated.
- Delete the brackets around the values so you're left with only a list.
- Enter the CONCATENATE formula before the values, and close the formula with a parenthesis.
- Hit enter to see your new text string.
While certain heavy Excel users say the CONCATENATE function is becoming outdated, it's still a useful shortcut for combining text strings without impacting the rest of your worksheet. If you're new to the program, take the time to learn more about how to use Excel and check out these helpful keyboard shortcuts. You'll be whizzing your way around worksheets and working more efficiently in no time.
