Using the CONCATENATE Function

Hands of Unity - Image by John Hain from Pixabay.
  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

This post will teach you how to use the CONCATENATE function in Excel. We will cover what it is used for, its syntax, examples which apply it, its associated frequent problems, and also best practices of using it. With CONCATENATE which is one of multiple text functions, you can combine two or greater text strings into a single string.

Important: The CONCAT function has substituted this function in Excel 2016, Excel Mobile, and Excel for the web. Even though the CONCATENATE function is readily available for backward compatibility, your first port of call should be to use CONCAT going forward. This is due to the possibility that CONCATENATE might be absent from upcoming versions of Excel.

Syntax: CONCATENATE(text1, [text2], …)

For example:

  • =CONCATENATE(“Segment population for “, A2, ” “, A3, ” is “, A4, “/mile.”)
  • =CONCATENATE(B2, ” “,C2)
Argument nameDescription
text1    (required)The initial item to join. The item can comprise either a text value, number, or cell reference.
Text2, …    (optional)Extra text items to join. You can own no more than 255 items, reaching a total of 8,192 characters.
Hands of Unity symbolising the CONCATENATE Function.
Image by John Hain from Pixabay.

CONCATENATE Function Examples

In order to experiment with these examples in Excel, copy the data in the displayed table, and paste it into cell A1 of a brand new worksheet.

Data
brook troutAndreasHauser
speciesFourthPine
32
FormulaDescription
=CONCATENATE(“Stream population for “, A2, ” “, A3, ” is “, A4, “/mile.”)Generates a sentence by linking the data in column A with other text. The result is Stream population for brook trout species is 32/mile.
=CONCATENATE(B2, ” “, C2)Connects three things: the string in cell B2, a space character, and the value in cell C2. The result is Andreas Hauser.
=CONCATENATE(C2, “, “, B2)Combines three things: the string in cell C2, a string with a comma and a space character, and the value in cell B2. The result is Andreas, Hauser.
=CONCATENATE(B3, ” & “, C3)Connects three things: the string in cell B3, a string including a space with ampersand and another space, and the value in cell C3. The result is Fourth & Pine.
=B3 & ” & ” & C3Links the equivalent items as the past example, but by using the ampersand (&) calculation operator rather than the CONCATENATE function. The result is Fourth & Pine.

Integration - Welcome shaking hands - represents the CONCATENATE Function.
Image by Gerd Altmann from Pixabay.

Common Problems of the CONCATENATE Function

ProblemDescription
Quotation marks appear in result string.Use commas to split adjoining text items. For example: Excel will present =CONCATENATE(“Hello “”World”) as Hello”World with an additional quote mark since a comma amid the text arguments was left out. Numbers are not required to contain quotation marks.
Words are jumbled together.Without specified spaces between independent text entries, the text entries will operate in partnership. Insert additional spaces within the CONCATENATE formula. There are two approaches to do this:
1. Append double quotation marks enclosing a space between them ” “. For example: =CONCATENATE(“Hello”, ” “, “World!”).
2. Insert a space following the Text argument. For example: =CONCATENATE(“Hello “, “World!”). The string “Hello ” contains an additional space inserted.
The #NAME? error appears instead of the expected result.#NAME? typically indicates that there are omitted quotation marks from a Text argument.

Best practices of CONCATENATE Function

Do thisDescription
Use the ampersand & character instead of the CONCATENATE function.The ampersand (&) calculation operator allows you to join text items which skips the need to use a function.
For example, =A1 & B1 yields the same value as =CONCATENATE(A1,B1). In several cases, employing the ampersand operator is faster and easier than using CONCATENATE to produce strings.
Find out more about using operation calculators.
Use the TEXT function to combine and format strings.The TEXT function transforms a numeric value to text and merges numbers with text or symbols.
For example, if cell A1 has the number 23.5, you can apply this particular formula to format the number as a dollar amount:
=TEXT(A1,”$0.00″)Result: $23.50

Related

Leave a Reply

%d bloggers like this: