- Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2019 for Mac.
This post will teach you how to use the CONCAT function (how it is formatted), essential conditions for it to work, some examples. There will also be some further links directing you to similar topics to explore at your own pace.
Using the CONCAT function enables you to integrate text from several ranges and/or strings. However, it fails to offer delimiter or IgnoreEmpty arguments.
CONCAT is now the successor of the CONCATENATE function. Despite this, the CONCATENATE function will remain accessible for compatibility purposes with former incarnations of Excel.
Syntax of the CONCAT Function
|Text item to be linked. A string, or array of strings, like a range of cells.|
|Further text items to be joined. There can be up to 253 text arguments for the text items. Each specific text item can comprise a string, or array of strings, including a range of cells.|
For example, =CONCAT(“The”,” “,”sun”,” “,”will”,” “,”rise”,” “,”tomorrow.”) will return The sun will rise tomorrow.
Tip: To consider delimiters (like spacing or ampersands (&)) within your sought text to merge, and to delete empty arguments you prefer to be excluded from the combined text result, you can use the TEXTJOIN function.
Remarks of the CONCAT Function
- If the resulting string is greater than 32,767 characters (cell limit), CONCAT brings back the #VALUE! error.
Examples of the CONCAT Function
Copy the sample data in all the displayed tables, and paste them into cell A1 of a brand new Excel worksheet. To get formulas to display results, pick them, press F2, and then press Enter. If at any point you can only see part of your data, then you can simply modify your column widths to gain a fuller picture of all the data.
Since this function enables entire column and row references, it yields this result: A’sa1a2a4a5a6a7B’sb1b2b4b5b6b7
|Data||First Name||Last name|
|=CONCAT(“Stream population for “, A2,” “, A3, ” is “, A4, “/mile.”)||Generates a sentence by joining the data in column A with other text.||Stream population for brook trout species is 32/mile.|
|=CONCAT(B2,” “, C2)||Links three things: the string in cell B2, a space character, and the value in cell C2.||Andreas Hauser|
|=CONCAT(C2, “, “, B2)||Connects three things: the string in cell C2, a string with a comma and a space character, and the value in cell B2.||Hauser, Andreas|
|=CONCAT(B3,” & “, C3)||Links three things: the string in cell B3, a string consisting of a space with ampersand and another space, and the value in cell C3.||Fourth & Pine|
|=B3 & ” & ” & C3||Connects the same items as the previous example, but by using the ampersand (&) calculation operator rather than the CONCAT function.||Fourth & Pine|