CONCATENATE Function in Microsoft Excel, OpenOffice Calc, Google Sheets, and Apple Numbers combines two or more text strings into one string.
Important Note: CONCATENATE Function has been replaced in recent versions of Excel with CONCAT Function. Hence, in Excel 2016, Excel Mobile, and Office 365, you need to use the CONCAT Function. No need to worry, as of now the CONCATENATE function is available in older versions for compatibility.
The CONCATENATE function is used to combine (concatenate) multiple text strings into a single string. It allows you to join two or more text strings together, whether they are constants, cell references, or a combination of both.
For Example, Cell A1 contains “App” and B1 contains “le”. By using the CONCATENATE Function, we can get “Apple” in cell C1.
If there are sentences or different words, you must add an argument of [” “] or you can include an extra space before or after the word.
For example, Cell A1 consists of “MSOffice” and B1 consists of “Geek”. To get results as “MSOffice Geek” you need to add an argument with [” “] or you can include an extra space before the word ” Geek” in Cell B1 or after the word “MSOffice “.
You can also use the ampersand operator “&” to achieve the same result instead of using the CONCATENATE Function. Taking the same example above, you can use =”MSOffice ” & “Geek”.
You can also check out other Spreadsheet Functions like AVERAGE Function, CEILING Function, MAX Function, MIN Function, NUMBERTEXT, SpellNumber, and SpellNumber Indian Rupees on our Functions Page.
So, let us figure out how to use the CONCATENATE Function in each spreadsheet program.
Table of Contents
How To Use The CONCATENATE Function in Excel?
To use the CONCATENATE function in Excel, follow these steps:
- Open Microsoft Excel and navigate to the cell where you want to display the concatenated text.
- In the cell, type the following formula =CONCATENATE(text1, [text2], …)
- Replace text1, text2, and so on, with the actual text strings or cell references you want to concatenate. You can include as many text strings or cell references as needed, separating them with commas.
- Press Enter to calculate the formula. The concatenated text will appear in the cell.
If you are using the ampersand (&) operator as a shorthand for concatenation, then follow these steps:
- Open Excel and select the cell where you want to display the concatenated text.
- In the cell, type the following formula =text1 & [text2] & …
- Replace text1, text2, and so on, with the desired text strings or cell references. Separate them with ampersands (&).
- Press Enter to calculate the formula. The concatenated text will appear in the cell.
SYNTAX
CONCATENATE(text1, [text2], [text3], …)
Where:
text1, [text2], …: These are the text strings you want to concatenate. They can be entered directly as text enclosed in double quotation marks (“text”), or they can be cell references that contain text.
Note that the arguments enclosed in square brackets ([ ]) are optional. You can choose to include them or omit them based on your requirements. If you omit an argument, the CONCATENATE function will ignore it.
You can have up to 255 arguments, up to a total of 8,192 characters.
How To Use The CONCATENATE Function in OpenOffice Calc?
In OpenOffice Calc, you can use the CONCATENATE function to combine text strings in a similar way to Excel. Here’s how you can use the CONCATENATE function in OpenOffice Calc:
- Open OpenOffice Calc and navigate to the cell where you want to display the concatenated text.
- In the cell, type the following formula =CONCATENATE(text1; text2; …).
- Replace text1, text2, and so on with the text strings or cell references you want to concatenate. Use semicolons (;) to separate the text strings or cell references.
- Press Enter to calculate the formula. The concatenated text will appear in the cell.
Alternatively, you can use the ampersand (&) operator as a shorthand for concatenation just like Excel.
SYNTAX
CONCATENATE(text1; text2; …)
Where:
text1; [text2]; …: These are the text strings you want to concatenate. They can be entered directly as text enclosed in double quotation marks (“text”), or they can be cell references that contain text.
In OpenOffice Calc, you can use up to 30 arguments in the CONCATENATE function. This means you can concatenate up to 30 text strings or cell references using a single CONCATENATE function.
How To Use The CONCATENATE Function in Google Sheets?
To use the CONCATENATE function in Google Sheets, follow these steps:
- Open Google Sheets and navigate to the cell where you want to display the concatenated text.
- In the cell, type the following formula =CONCATENATE(string1, [string2, …])
- Replace text1, text2, and so on with the text strings or cell references you want to concatenate. Separate the text strings or cell references with commas (,).
- Press Enter to calculate the formula. The concatenated text will appear in the cell.
Alternatively, you can use the ampersand (&) operator as a shorthand for concatenation just like Excel and OpenOffice Calc.
SYNTAX
=CONCATENATE(string1, [string2, …])
Where:
string1, [string2, …]…: The strings refer to the information that you want to combine. They can be an individual cell, a range, or a specified text. Any string beyond the first string is optional.
In Google Sheets, the CONCATENATE function allows you to concatenate up to 50 text strings. This means you can include a maximum of 50 arguments within the CONCATENATE function to join multiple text strings together.
How To Use The CONCATENATE Function in Apple Numbers?
In Apple Numbers, you can use the CONCATENATE function to combine text strings. Here’s how you can use the CONCATENATE function in Apple Numbers:
- Open Apple Numbers and navigate to the cell where you want to display the concatenated text.
- In the cell, type the following formula =CONCATENATE(text1, text2, …)
- Replace text1, text2, and so on with the text strings or cell references you want to concatenate. Separate the text strings or cell references with commas (,).
- Press Enter to calculate the formula. The concatenated text will appear in the cell.
Alternatively, you can use the ampersand (&) operator as a shorthand for concatenation similar to Excel.
SYNTAX
CONCATENATE(text1, text2, …)
Where:
text1, [text2], …: These are the text strings you want to concatenate. They can be entered directly as text enclosed in double quotation marks (“text”), or they can be cell references that contain text.
In Apple Numbers, the CONCATENATE function allows you to concatenate up to 255 text strings. This means you can include a maximum of 255 arguments within the CONCATENATE function to join multiple text strings together.
Possible Errors
While using the CONCATENATE function in spreadsheet applications like Excel, OpenOffice Calc, Google Sheets, or Apple Numbers, you may encounter the following errors on the screen:
#VALUE!: This error typically occurs when one or more of the arguments within the CONCATENATE function are not valid. For example, if you’re trying to concatenate a numeric value without converting it to text first.
#DIV/0!: This error occurs if one of the cell references within the CONCATENATE function contains a division by zero error. Check the referenced cells to ensure there are no zero denominators.
#REF!: This error appears if one of the cell references within the CONCATENATE function refers to a cell that has been deleted or is outside the valid range. Check and update the cell references as needed.
#NAME?: This error occurs if the CONCATENATE function is not recognized by the spreadsheet application. Double-check that you are using the correct function name and that it is spelled correctly.
Circular Reference Warning: If you create a circular reference by referencing the cell in which the CONCATENATE function is located, you may receive a circular reference warning. This warning indicates that the formula may not calculate as expected due to the circular reference.
Truncated Text: If the concatenated result exceeds the character limit of a cell, some spreadsheet applications may truncate the excess characters. You may not see the complete concatenated text in the cell, which can lead to unexpected results.
These error messages and warnings can provide valuable insights into the nature of the error, helping you identify and correct any issues within your CONCATENATE function or the referenced cells.
Don’t forget to share this informative article with your friends and colleagues on various social media platforms like Instagram and Facebook to let them know about its usefulness. Remember, sharing is caring and it is the right way to help others.
Moreover, if you have any doubts or suggestions feel free to write in the comment box below. We will get back to you soon.