Concatenate

Use CONCATENATE, one of the text functions, to join two or more text strings into one string.

Syntax

CONCATENATE(value1, [value2], ...)

For example:

=CONCATENATE("Stream population for ", A2, " ", A3, " is ", A4, "/mile.")
=CONCATENATE(B2, " ",C2)

Argument name	Description
value1 (required)	The first item to join. The item can be a text value, number, or cell reference.
value2, ... (optional)	Additional text items to join. You can have up to 255 items, up to a total of 8,192 characters.

Examples

Set up 7 fields with the following Names and Default Values:

Field 1: brook trout
Field 2: Andreas
Field 3: Hauser
Field 4: species
Field 5: Fourth
Field 6: Pine
Field 7: 32

Formula	Description
=CONCATENATE("Stream population for ", [Field 1], " ", [Field 4], " is ", [Field 7], "/mile.")	Creates a sentence by joining the data in fields 1, 4, and 7. The result is Stream population for brook trout species is 32/mile.
=CONCATENATE([Field 2], " ", [Field 3])	Joins three things: the string in cell [Field 2], a space character, and the value in cell [Field 3]. The result is Andreas Hauser.
=CONCATENATE([Field 3], ", ", [Field 2])	Joins three things: the string in cell [Field 3], a string with a comma and a space character, and the value in cell [Field 2]. The result is Andreas, Hauser.
=CONCATENATE([Field 5], " & ", [Field 6])	Joins three things: the string in cell [Field 5], a string consisting of a space with ampersand and another space, and the value in cell [Field 6]. The result is Fourth & Pine.
=[Field 5] & " & " & [Field 6]	Joins the same items as the previous example, but by using the ampersand (&) calculation operator instead of the CONCATENATE function. The result is Fourth & Pine.

Common Problems

Problem	Description
Quotation marks appear in result string.	Use commas to separate adjoining text items. For example: Excel will display =CONCATENATE("Hello ""World") as Hello"World with an extra quote mark because a comma between the text arguments was omitted. Numbers don't need to have quotation marks.
Words are jumbled together.	Without designated spaces between separate text entries, the text entries will run together. Add extra spaces as part of the CONCATENATE formula. There are two ways to do this: Add double quotation marks with a space between them " ". For example: =CONCATENATE("Hello", " ", "World!"). Add a space after the Text argument. For example: =CONCATENATE("Hello ", "World!"). The string "Hello " has an extra space added.

Problem

Description

Quotation marks appear in result string.

Use commas to separate adjoining text items. For example: Excel will display =CONCATENATE("Hello ""World") as Hello"World with an extra quote mark because a comma between the text arguments was omitted.

Numbers don't need to have quotation marks.

Words are jumbled together.

Without designated spaces between separate text entries, the text entries will run together. Add extra spaces as part of the CONCATENATE formula. There are two ways to do this:

Add double quotation marks with a space between them " ". For example: =CONCATENATE("Hello", " ", "World!").
Add a space after the Text argument. For example: =CONCATENATE("Hello ", "World!"). The string "Hello " has an extra space added.

Best Practices

Do this	Description
Use the ampersand & character instead of the CONCATENATE function.	The ampersand (&) calculation operator lets you join text items without having to use a function.For example,=[Field 1] & [Field 2] returns the same value as=CONCATENATE([Field 1],[Field 2]). In many cases, using the ampersand operator is quicker and simpler than using CONCATENATE to create strings.