Before you have hundreds of data points that need to be combined and reformatted manually, copy and paste shortcuts are very convenient.
Fortunately, you can use the CONCATENATE Excel function to save time (and suppress carpal tunnel) when concatenating information from cells, rows, or columns.
It means “connected together” or “combined”. Joining is a method of combining the contents of two or more cells into one cell.
This function allows you to combine data in columns, cells, ranges, and rows into any format you want, making it easy to quickly connect names and addresses or display the date and time correctly.
There are multiple ways to set the CONCATENATE formula in Excel, so we will walk through the steps required to use this function and achieve the goals of the worksheet.
How to connect in Excel
Although there are many ways to combine text in an Excel worksheet, we will focus on the CONCATENATE function.
It merges the contents of two or more cells into one cell without physically changing the shape of the cell. It is usually used to connect text fragments (called Text string or String) From a single cell to a cell. The generated text string is a combination of all the strings in the CONCATENATE formula.
Let’s see how to connect in Excel:
Suppose you have a list of customers with separate first and last names. You need everyone’s full name to build a redirect marketing campaign, so you want to concatenate the text in column A (first name) with the text in column B (last name).
Before using the CONCATENATE function, you must create a new column for the combined text. In the example below, it is column C (full name).
Now you are ready to connect the first name and last name. To do this, you need to understand the syntax of the function and how to format the text strings that make up the formula.
Excel connection formula
Like all Excel functions, the CONCATENATE formula begins with an equal sign (=), followed by the function name, opening parenthesis, and text parameters. No need to get excited-in this case, the parameter just tells the formula which cells to combine.
= Connection (text 1, [text2], …)
=CONCAT(Text 1, [text2], …)
Text 1: This is the first parameter to be added, which can be a number, text, or cell reference.
Text 2, Text 3, etc.: These are additional items to be combined. The CONCATENATE formula can connect up to 255 items with a total of 8,192 characters.
notes: In all versions of Excel 2016 and later, the CONCATENATE function has been replaced with CONCAT functionThis function works in exactly the same way, adding the ability to combine text within a range of cells (ie =CONCAT(A2:D8)). Although you can still access the CONCATENATE function for compatibility reasons, Microsoft warns that CONCATENATE may not be available in future versions of Excel.
Excel connects with spaces
Double quotation marks (” “) include a space you want between the text parameters. Remember to insert a comma before and after each set of quotation marks, otherwise an error message will pop up and the formula will not run. If you press the “Enter” key, you will know that your formula is correct and the new text string appears exactly as you wish.
In the name example, you want to merge the text in cells A16 and B16, so add these parameters to the formula. Make sure to place the parameters in the order you want them to appear. To get the “first name and last name”, set the CONCATENATE formula like this:
=Connect ((B16, “”, A16)
notes: If at least one parameter of the CONCATENATE function is invalid, the formula will return #VALUE! error.
It is time to apply the CONCATENATE formula to each name in the list. Just hover your mouse over the combined cell until the plus sign (+) appears in the lower right corner. Then click and hold while dragging the cursor down in column C to highlight all the cells you want to join.
When you release the cursor, voila! The formula applies to each cell. This allows you to concatenate dozens of text strings at once, eliminating the need to type formulas for each cell.
Excel connection string
No matter 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 you can create longer, more meaningful text strings in Excel. The key to this is to ensure that your results provide value to anyone who uses the information.
Suppose you are running an email marketing campaign and want to personalize the subject line with each customer’s name. You can use the CONCATENATE function to combine text strings instead of manually entering the name after the name.For this example, I used a Attractive email subject line From Warby Parker.
I wrote the following formulas, making sure to include commas and spaces where I want them to appear.
=CONCATENATE(B2,”,”, A2, “,”,” “, C2)
Then I applied the formula to the entire column to generate my personalized list of subject lines.
When you are experimenting with longer text strings, be aware that each cell reference in the CONCATENATE function must be listed separately, because it does not recognize arrays. For example, your formula should be similar to =CONCATENATE (B1, B2, B3, B4) instead of =CONCATENATE(B1:B4).
Combining text is simple, but mixing the date and time can lead to confusion and error-ridden results.
Connect date and time in Excel
You can prevent time and date issues by embedding the TEXT function in the CONCATENATE Excel formula. This will allow you to control the format when combining text with numbers or dates.
Let’s see how to combine all this information. In this worksheet, I want to record the date and time each blog post went live so that I can refer to this information in my monthly performance analysis.
The formula required to concatenate these three strings may seem complicated, but once you decide how to format the date and time, it’s easy. I want to keep the date and time, so I include the date (mm/dd/yyyy) The sum time (hh:mm:ss) is formatted as 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 the date and time the post was published.
Use the same steps as before to apply the function to the rest of the worksheet. Drag the plus sign (+) in the lower right corner of the combined cell to the column to highlight the cell to be connected.
Excel connection range
If you are excited about your new Excel skills, you may be eager to invest and organize worksheets with thousands of data points. Using the CONCATENATE function can speed up your work, but be aware of limitations.
Excel only allows you to use the CONCATENATE function to combine 255 items with a maximum of 8,192 characters. Therefore, if you want to create hundreds (or thousands) of new text strings, you must work in segments.
The CONCATENATE function does not accept arrays (A3:E5), so be sure to list every parameter you want to include in the formula. If you need to combine tens or hundreds of cells, please try one of these shortcuts.
Use the control keys.
- Enter the first part of the formula =CONCATENATE(
- Hold down the CONTROL key and select a single cell to be merged.
- Release the CONTROL key, type the right parenthesis, and press 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 and then exchange it with a single cell.
- Select the cell where you want to use the CONCATENATE function.
- Enter the TRANSPOSE formula to generate a cell array. It looks similar to: =TRANSPOSE(A1:A10)
- After selecting the TRANSPOSE cell, press the F9 key in the edit bar to replace the array with each value to be connected.
- Remove the parentheses around the value, so you only have one list left.
- Enter the CONCATENATE formula before the value and close the formula with parentheses.
- Press Enter to see your new text string.
Although some heavy users of Excel indicate that the CONCATENATE function is obsolete, it is still a useful shortcut for combining text strings without affecting the rest of the worksheet.If you are new to the program, please take the time to learn more about How to use Excel Look at these useful Keyboard shortcuts. You will quickly move around the work table and immediately increase your work efficiency.