|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

CONCAT

CONCAT

  • Last updated on
Related to spreadsheet functions

Description

Use this function to concatenate (assemble) the contents of one or more cells plus optional characters into a single cell.

Syntax

CONCAT(text1, …, [text253])

Inputs

This function accepts the following argument:

Name Required Description Valid Input
text1 Yes The text or cell value to be added. A text string, a cell ID, a cell range, or a formula which results in any of these.
text2, …, text253 No The text or cell value to be added. A text string, a cell ID, a cell range, or a formula which results in any of these. CONCAT can handle up to 254 text arguments.

Examples

Sample data

Data set 1

A B C D E F
1 First Name Last Name Email Joined Area Code Tel Number
2 George Smith george.smith@gmail.com 12/4/2024 415 482-7391
3 Lina Escobar Escobar_L@yahoo.com 1/6/1997 801 615-2840
4 Ezsther Klein EstherK@juno.com 3/5/2002 213 927-5613
5 Don Ffolkes D_Ffolks@mail.co.uk 6/9/2012 503 354-8092
6 Maria Empenada m.empenada45@telefonica.es 11/5/2021 245 768-4325

Data set 2

A B C D E F G
1 Name Tigers Eagles Cougars Bears Ravens Lions
2 Score 8 5 7 7 5 6

Sample formulas

Use case Formula Result
Combine the content of cells A2 and B2. =CONCAT(A2:B2)

Using data set 1, this produces the following:

GeorgeSmith
Combine the content of cells A2 and B2, adding a space between the contents of the two cells. =CONCAT(A2, " ", B2)

Using data set 1, this produces the following:

George Smith
Combine the content of multiple cells as separate words, then adding a fixed text string at the end. =CONCAT(G1, ", ", B1, ", and ", E1, ", oh my!")

This formula combines the content of cells G1, B1, and E1, adding a space between the contents of the cells, then adding the text "oh my!" at the end.

Using data set 2, this produces the following:

Lions and Tigers and Bears, oh my!
Combine the content of multiple cells described by a range. =CONCAT(B2:G2)

This formula combines the content of all the cells in the range B2:G2.

Using data table 2, this produces the following:

857756
Combine Area code and telephone number into a single string. =CONCAT(E2, "-", F2)

This formula combines the content of cells E2 and F2, inserting a hyphen between the contents of the two cells.

Using data set 1, this produces the following:

415-482-7391
Take the first letter of the first name in A2 and the first letter of the last name in B2, combining them into initials. =CONCAT(LEFT(A2,1), LEFT(B2,1))

This formula first uses the LEFT command twice to take the first (1) character from the specified cells and CONCAT joins these into a single string. If this formula was in cell E1, the result would be:

GS
Combine the first name and the related surname in uppercase, then add the text from cell D1 and append the year from the date in D2. =CONCAT(A2, " ", UPPER(B2), ", ", $D$1, ": ", YEAR(D2))

This formula uses CONCAT to gather together the results of the operations inside the outer parens "()". 
These operations work as follows: first the first name is collected as-is from cell A2, then a space is inserted, then UPPER converts the last name (cell B2) to uppercase. A comma and a space is appended, and the text from D1 is inserted, followed by a colon and space. Finally, YEAR extracts the year from the date in D2. The reference to D1 allows for the "Joined" text to be altered, for example to "Member since:".

Using data set 1, this produces the following:

George SMITH, Joined: 2024

Notes

  • Microsoft created CONCAT to replace and improve on CONCATENATE. However, there are some significant differences:   
    • Range support: CONCAT can work with cell ranges, allowing you to combine multiple cells in a single operation (for example:, CONCAT(A1:A10)), while CONCATENATE requires individual cell references.
    • Syntax simplicity: CONCATENATE offers a simpler syntax for combining multiple cells, especially when working with ranges.
    • Performance: In some cases, CONCATENATE may outperform the concatenation operator (&) for a large number of arguments, but CONCAT is generally more efficient for working with ranges.
    • Backwards compatibility: CONCATENATE is still supported for compatibility with earlier Excel versions, while CONCAT may not be available in older versions.
  • CONCAT:   
    • Does not automatically add spaces between the text strings. You must include spaces as part of the text arguments if needed.
    • Automatically converts numbers to text.
    • Treats empty cells as empty strings.
  • Unlike TEXTJOIN, CONCAT does not allow you to define a common delimiter between items. You must specify each delimiter individually in the formula.

Tips

  • The ampersand (&) operator functions as an alternative to CONCAT. For example: =A2 & " " & B2.
  • Combining CONCAT with other functions such as LEFT, RIGHT, or MID permits more complex text manipulations.

Related functions

Here are the other array formula functions supported in Workiva spreadsheets. 

Was this article helpful?
0 out of 0 found this helpful
DescriptionUse this function to concatenate (assemble) the contents of one or more cells plus optional characters int...