How to use the CONCAT Function?

Computer code - Image by Free-Photos from Pixabay.
  • 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. 

Note: This feature is found on Windows or Mac if you have Office 2019, or if you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.

A friendship group representing the CONCAT Function.
Image by Bob Dmyt from Pixabay.

Syntax of the CONCAT Function

CONCAT(text1, [text2],…)

ArgumentDescription
text1
(required)
Text item to be linked. A string, or array of strings, like a range of cells.
[text2, …]
(optional)
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.

Example 1

=CONCAT(B:B, C:C)A’sB’s
a1b1
a2b2
a4b4
a5b5
a6b6
a7b7

Since this function enables entire column and row references, it yields this result: A’sa1a2a4a5a6a7B’sb1b2b4b5b6b7

Example 2

=CONCAT(B2:C8)A’sB’s
a1b1
a2b2
a4b4
a5b5
a6b6
a7b7

Result: a1b1a2b2a4b4a5b5a6b6a7b7

Example 3

DataFirst NameLast name
brook troutAndreasHauser
speciesFourthPine
32
FormulaDescriptionResult
=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 & ” & ” & C3Connects the same items as the previous example, but by using the ampersand (&) calculation operator rather than the CONCAT function.Fourth & Pine

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

CONCATENATE function

TEXTJOIN function

Overview of formulas in Excel

How to avoid broken formulas

Find and correct errors in formulas

Excel keyboard shortcuts and function keys

Text functions (reference)

Excel functions (alphabetical)

Excel functions (by category)

Leave a Reply

%d bloggers like this: