Our tutorial on the Concatenate function will let you learn about the different ways you can use the Concatenate function, as well as the “&” operator, to have rows, cells, columns, ranges, and text strings concatenated in Excel.
In Excel, the information in the workbooks may not always have a structure that fits your needs.
Usually, you would want to have the content of a cell split into individual cells.
You may also want to do the opposite, which involves combining data from more than two columns into just one column.
Some of the common examples would require concatenation in Excel to join address parts and names.
It allows the text to combine with a value that the formula.
It has been driven to display the desired format of the times and dates, to mention a few.
Our tutorial will explore the different string concatenation techniques in Excel that will allow you to select the most suitable method for your worksheets.
In Excel, what is “concatenate?”
There are essentially a couple of ways on how you can combine the information in Excel spreadsheets.
You can do this by any of the following:
- Concatenate the values of the cells
- Merge cells
Whenever you use Excel to concatenate cells, you will only have those cells’ contents combine.
In Excel, concatenation involves the process of having two or more values joined together.
People usually use this method to connect some text pieces in various cells that are technically referred to as simply strings or text strings.
In the middle of some of the text, you may also insert a value that is calculated by formula.
Whenever you work on merging cells, you are “physically” working on integrating more than one cell into a single cell.
Because of that, it will allow you to have a larger cell that gets displayed across more than one column or row in your worksheet.
Here is a screenshot that demonstrates how these methods are different:
This tutorial will tackle the two crucial ways to concatenate Excel strings through the Excel & operator and the CONCATENATE function.
The CONCATENATE function in Excel
Excel has the CONCATENATE FUNCTION designed to join various text pieces together or have values of several cells combined into one cell.
Here is Excel CONCATENATE’s syntax:
CONCATENATE(text1, [text2], …)
Whenever the text is a cell reference, formula-driven value, or text string, here are some examples of how you can use Excel’s CONCATENATE function.
In Excel 2019 and Excel 2016, Excel Mobile and Excel Online CONCATENATE got replaced with the function CONCAT that has the same syntax.
Even if they kept the CONCATENATE function for backward compatibility, they recommend using CONCAT as Microsoft doesn’t promise that CONCATENATE will become available in Excel’s future versions.
Have a formula-calculated value and text string concatenated
If you want to return the result with a formula that your users can understand, you can have it concatenated with a text string to explain the actual value.
As an example, this is the formula that you can use to go back to the date today:
=CONCATENATE(“Today is “,TEXT(TODAY(), “dd-mmm-yy”))
Tip. Whenever you want to remove the data source without creating an effect on the resulting text strings, you can use the option “Paste special – values only” if you want to have the formulas converted to their values.
Concatenating several cells’ values
If you want to combine the values that cells A1 and B1 have, here is the most straightforward formula to CONCATENATE:
Take note that you need to knit the values together without using any delimiter.
It should be like in the 2nd row of this screenshot.
If you want to have the values separated with space, have ” ” entered in the second argument as what you can see in the 3rd row of this screenshot:
=CONCATENATE(A1, ” “, B1)
You can have the concatenated values separated with other delimiters like a slash, space, or comma.
Concatenating a cell value and text string
You don’t have any reason to limit the Excel CONCATENATE function to only joining the values of cells.
You can have it used to concatenate different text strings so that the result will become more meaningful.
As an example, the formula =CONCATENATE(A1, ” “, B1, ” completed”) tells the user that a specific project is complete, like in the 2nd row of the screenshot below.
There is a space added before the term “completed,” as this works to have the concatenated text strings separated.
You can also naturally include a text string in the middle or at the start of your Concatenate formula:
=CONCATENATE(“See “, A1, ” “, B1)
In between the values combined is space (” “) added to make the result display “Project 1” instead of “Project1.”
Things To Remember When Using Excel’s CONCATENATE
Remember to follow these simple rules so you can make sure that your CONCATENATE formulas will always provide the right results:
- The formula will return a #VALUE! Error whenever there is at least one invalid argument in the CONCATENATE function.
- You can have a maximum of 255 strings concatenated in one CONCATENATE formula that has a sum of 8,192 characters.
- If you want the Excel CONCATENATE function to work, it needs a minimum of one “text” argument.
- Excel CONCATENATE does not recognize arrays. Thus, it would be best if you had every cell reference listed separately. For example, instead of writing =CONCATENATE(A1:A3), you must write =CONCATENATE(A1, A2, A3).
- Even if every source value is a number, the CONCATENATE function’s result will always be a text string.
Concatenating Excel Strings with the “&” Operator
Another way you can concatenate cells in Microsoft Excel is the & operator. It is a method that has been quite handy in numerous scenarios, as typing the ampersand sign (&) can be so much faster than having to type the word “concatenate.”
Just like the function CONCATENATE, YOU CAN HAVE “&” used in Excel whenever you want to have different text strings combined and return results and cell values by other processes.
Examples of the “&” Excel Formula
If you want to see the operator concatenation in action, you can have the CONCATENATE formulas re-written as previously discussed:
Have the values in A1 and B1 concatenated and separated with a space:
Make the values in A1 and B1 concatenated:
Have the result of the TEXT / TODAY function and the string concatenated:
=”Today is ” & TEXT(TODAY(), “dd-mmm-yy”)
Make the text string and the values in A1 and B1 concatenated:
=A1 & B1 & ” completed”
Just as what this screenshot demonstrates, the “&” operator and the CONCATENATE function will return similar results:
The CONCATENATE Function VS. the Excel “&” Operator
Many users have wondered which can be more efficient in concatenating Excel strings. It could be the “&” operator or CONCATENATE function.
The sole vital difference between the “&” operator and CONCATENATE is the Excel CONCATENATE function’s 255 string limit that does not apply to the use of the ampersand.
Besides that, there may be no difference between these concatenation methods.
There is also no difference in speed between the “&” and CONCATENATE formulas.
Since 255 can be quite a large number, that someone may never have to combine this many strings in real life, the difference can boil down to the ease and comfort of use.
Some users consider CONCATENATE formulas as something easy to read. Personally, using the method “&” is preferred.
Because of that, you need to rely on the concatenation technique that is most comfortable for you.
Concatenating the cells that have a comma, space, and other characters
There may be worksheets where you have to join the values with different punctuation marks, spaces, commas, or other characters like the slash or hyphen.
Do this by including the character that you want to be included in your formula for concatenation.
Just remember to have that character enclosed in quotation marks as what these examples demonstrate:
Using a comma to concatenate two cells:
=CONCATENATE(A1, “, “, B1) or =A1 & “, ” & B1
Using a hyphen to concatenate two cells:
=CONCATENATE(A1, “-“, B1) or =A1 & “-” & B1
Using a space to concatenate two cells:
=CONCATENATE(A1, ” “, B1) or =A1 & ” ” & B1
Here is a screenshot that demonstrates how the results would appear:
Use line breaks to concatenate text strings
Often, it would help if you had concatenated text strings separated with spaces and punctuation marks.
However, there are some cases wherein you have to use a carriage return or a line break to separate the values.
The merging of mailing addresses from information in different columns is a typical example of this.
The issue with this is that you may not simply have the line break typed in the formula similar to a usual character.
Because of that, you need the particular CHAR function so that the corresponding ASCII code will get supplied to the concatenation formula:
- For those who use the Mac system, use CHAR(13). Here, the ASCII code for Carriage return is 13.
- For people who are using Windows, utilize CHAR(10). Here, the Line feed‘s ASCII code is 10.
This example needs to have the pieces in columns A to F addressed.
With the use of the “&” concatenation operator, they are placed together in the G column.
The space (” “), line break CHAR(10), and comma (“, “) separates the merged values:
=A2 & ” ” & B2 & CHAR(10) & C2 & CHAR(10) & D2 & “, ” & E2 & ” ” & F2
Take note that whenever you use line breaks to have concatenated values separated, you need to enable the “Wrap Text” option to display the correct result.
Do this by pressing Ctrl + 1, so you can have the Format Cells dialog opened.
After that, go to the tab Alignment and review the Wrap text box.
In the same way, allow concatenated strings to separate with other characters like:
- Forward slash (/) – CHAR(47)
- Asterisk (*) – CHAR (42)
- Double quotes (“) – CHAR(34)
- There is a full list of available ASCII codes.
However, the easier way to have printable characters included in the concatenation formula is to have them typed in double-quotes, just like what we did in an earlier example.
All of these four formulas will yield the same results:
=A1 & “/” & B1
=A1 & CHAR(47) & B1
=CONCATENATE(A1, “/”, B1)
=CONCATENATE(A1, CHAR(47), B1)
How to have columns concatenated in Excel
If you want to have two or more columns concatenated in Excel, you only need to have the usual formula in concatenation entered in the first cell.
After that, you can have it copied down to the other cells by dragging the fill handle.
This small square appears at the selected cell’s lower right-hand corner.
For example, you can have two columns (columns A and B) concatenated using a space to separate the values.
Then, have this formula entered in cell C2 and have it copied down to the other cells.
Whenever you drag the fill handle to have the formula copied, the mouse pointer will change to a cross as what is shown here:
You can quickly have the formula copied down to the other cells in the column by selecting the cell that has the formula and double-clicking the fill handle.
Take note that a fast way to have the formula copied down to the other cells of the column is to choose the cell that has the formula and then double-click on the fill handle.
Note that how far you can copy the cells once you double click on the fill handle is determined by Microsoft Excel.
It would be based on the cells referred to by the formula.
Whenever your table has empty cells like in this example where A6 and B6 are blank, the formula will only copy up to row 5.
Whenever this happens, you have to manually drag down the fill handle to have entire columns concatenated.
Alternatively, you can have Excel columns concatenated by using the Merge Cells add-in’s corresponding option.
Ways you can concatenate a cell range in Excel
You may need to make some effort to combine values from more than one cell, as the CONCATENATE function of Excel requires every argument to have a single cell reference and doesn’t accept arrays.
Have several cells concatenated from A1 to A4 by using any of these formulas:
=A1 & A2 & A3 & A4
=CONCATENATE(A1, A2, A3, A4)
It may not be great to have all references entered in the formula bar whenever you join a relatively small range.
It may be tedious to have an extensive range added as you must manually type every cell reference.
Here are the three methods on how you can use Excel for quick range concatenation:
Method 1. Use the add-in Merge Cells
You can quickly concatenate any Excel range free of formulas to use Excel’s add-in Merge Cells as the option “Merge all areas in selection” is turned off.
Method 2. Select multiple cells that you want to concatenate by pressing CTRL
If you want to choose more than one cell quickly, you can have the CTRL key pressed and click on every cell you want to be in the formula CONCATENATE.
These are the steps on how you can do this:
- Start by selecting a cell where you want to have the formula entered.
- In that cell or the formula bar, type in =CONCATENATE.
- Press and hold on to Ctrl and then click on every cell that you want to concatenate.
- Have the Ctrl button released by having the closing parenthesis typed in the formula bar and pressing Enter.
Take note that when you use this method, you need to click on every individual cell.
Use your mouse to choose a range with an array added to the formula that the CONCATENATE function may not accept.
Method 3. Get the range by using the function TRANSPOSE
Whenever you have to concatenate an extensive range that consists of tens or hundreds of cells, the past method may not be quick enough as you need to click on every cell.
For this case, it would be better to have the TRANSPOSE function used so you can return an array.
Then, use individual references of the cell to replace it in one swoop.
- Enter the TRANSPOSE formula in the cell where you want to have the concatenated range as an output. Here is an example: =TRANSPOSE(A1:A20)
- Press F9 in the formula bar to have the formula replaced with calculated values. Because of this, you will get a group of numbers that will get concatenated.
- Delete the curly braces that surround the array values.
- Before the initial value Type =CONCATENATE(. After the last value, type the closing parenthesis and then press Enter.
Note. Whatever method you want to use, C1’s concatenated value is a text string (it has a left-alignment in the cell) even if the original value is a number.
That is because the CONCATENATE function will always have a text string returned, whatever the source of the data type would be.
Have dates and numbers concatenated in different formats
Every time you want a text string concatenated with a date or a number, you may want to have the result formatted in a different way that could depend on your dataset.
You can do this by having the TEXT function embedded in the Excel concatenate formula.
There are a couple of arguments in the function TEXT(value, format_text):
- Value, the first argument, is where you have a date or number supplied for conversion to text or a reference to a cell with the numeric value.
- The format_text that is the second argument is where you can have your desired format entered using codes in a TEXT function that you can understand.
Let us remind you that you need to have the TEXT function used to help display the date in your desired format whenever you combine a date and a text string. As an example,
=CONCATENATE(“Today is “, TEXT(TODAY(), “dd/mm/yy”))
=”Today is ” & TEXT(TODAY(), “dd/mm/yy”)
Here are more examples of formulas that concatenate the number and text value:
=A2 & ” ” & TEXT(B2, “0.#”) – doesn’t display the $ sign and extra zeros
=A2 & ” ” & TEXT(B2, “# ?/???”) – allows the number to display as a fraction.
=A2 & ” ” & TEXT(B2, “$#,#0.00”) – lets the number get displayed with the $ sign and 2 decimal places
Ways You Can Split Cells (the opposite of Excel’s CONCATENATE)
Whenever you want to get CONCATENATE’s opposite in Excel as you want to have one cell split into several cells, here are some of the options available:
- Excel 2013 and 2016’s Flash Fill option
- Formulas (RIGHT, LEFT, and MID functions)
- The Feature Text to Columns
The Formula-Free Way to Concatenate Cells in Excel: Merge Cells Add-In
Since Merge Cells add-in is included in Excel’s Ultimate Suite, it can allow you to do these efficiently:
- Have the values of several cells concatenated into a single cell so they will get separated with your chosen delimiter.
- Make several cells merge into one without having to lose the data.
The tool Merge Cells can work well with all versions of Excel from 2003 to 2016. It can have all types of data combined, including special symbols, dates, numbers, and text strings.
Speed and simplicity are their two vital advantages.
You can have concatenation done in just a few clicks.
Here are the ways you can do it:
Have rows joined column-by-column
Allow for the combination of data column-by-column in every individual row by choosing to merge the Rows.
Select your desired delimiter (here, it is the line break), have the other settings configured on how you want it, and then click on the Merge button.
It will have a result that looks like this:
Make the values of several cells combined into a single cell.
Select the range that you want to concatenate and have the following settings configured to have the contents of cells combined:
- Put the cells into one and have them placed under “What to merge“;
- Choose your desired delimiter and have it placed under “Separate values with.” In this example, it’s a space and a comma;
- Select where you want to put the result;
- Have the option “Merge all areas in the selection” unchecked. This option will determine if the values of the cells are concatenated or if the cells are merged.
Have columns combined row-by-row
Have two or more columns concatenated by configuring the settings of Merge Cells similarly.
However, under “What to merge,” you need to select Columns:
Review how you can use the add-in Merge Cells to cope with your sets of data.
Become Aware of How to Concatenate Strings in Excel with ExcelMaster
Make yourself an Excel expert with us!
Get to know how you can use Excel to Concatenate Strings with ExcelMaster NOW!