The CONCAT function in Excel is used to combine text from multiple cells into one cell. It is commonly used to join first and last names, combine words with numbers, create IDs, or build readable text from separate pieces of information.
CONCAT is the newer version of CONCATENATE and works especially well when combining values across multiple cells or ranges. If you regularly clean data, create reports, or prepare exports, CONCAT can save a lot of time.
In This Guide, You’ll Learn
- What the CONCAT function does
- The syntax and arguments of CONCAT
- How to combine text from multiple cells
- Practical CONCAT examples for work and reporting
- Common mistakes when using CONCAT
- Real-world business uses for CONCAT
What is the CONCAT Function?
The CONCAT function combines text strings into a single text value. You can join words, numbers, cell values, or even ranges of cells into one result.
CONCAT Function Syntax
=CONCAT(text1, [text2], ...)Argument Definitions
- text1 – The first text item you want to combine.
- [text2] – Additional text items, cell references, or ranges to combine.
- You can include multiple arguments separated by commas.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> CONCAT Function Data
Basic CONCAT Formula
In this example, we combine a first name and last name into a full name.
Formulas Used
=CONCAT(A2," ",B2)=CONCAT(A3," ",B3)=CONCAT(A4," ",B4)=CONCAT(A5," ",B5)
How This Formula Works
- Excel takes the text in cell A2
- Adds a space (” “)
- And adds the text in cell B2
Without the space, the names would run together.
Combine Text with Numbers
CONCAT can combine words and numbers to create readable text.
Formulas Used
=CONCAT(A2," - Qty: ",B2)=CONCAT(A3," - Qty: ",B3)=CONCAT(A4," - Qty: ",B4)=CONCAT(A5," - Qty: ",B5)
How This Formula Works
- Excel takes the text in cell A2
- Adds a space and text within the quotes (” – Qty “)
- And adds the numbers in cell B2
This is useful for inventory tracking or dashboard labels.
Create Employee IDs
Many businesses combine text and employee numbers to create IDs.
Formulas Used
=CONCAT("EMP-",A2)=CONCAT("EMP-",A3)=CONCAT("EMP-",A4)=CONCAT("EMP-",A5)
How This Formula Works
- Excel adds EMP and a dash within the quotes (“EMP- “)
- And adds the numbers in cell B2
This helps standardize IDs across reports and systems.
Combine Multiple Columns into a Mailing Address
CONCAT can join address fields into a readable format.
Formulas Used
=CONCAT(A2,", ",B2,", ",C2)=CONCAT(A3,", ",B3,", ",C3)=CONCAT(A4,", ",B4,", ",C4)=CONCAT(A5,", ",B5,", ",C5)
How This Formula Works
- Excel takes the text in cell A2
- Adds a space (” “)
- Adds the text in cell B2
- Adds a space (” “)
- Adds the text in cell C2
This is commonly used when preparing customer mailing lists or exports.
Common CONCAT Mistakes
Forgetting Spaces
If you use:
=CONCAT(A2,B2)You may get:
JohnSmith
Instead, add a space:
=CONCAT(A2," ",B2)John Smith
Forgetting Quotation Marks
Text inside formulas must use quotation marks.
Correct:
=CONCAT(A2," - ",B2)Incorrect:
=CONCAT(A2, - ,B2)Using CONCAT Instead of TEXTJOIN
CONCAT joins text together but does not automatically insert separators between multiple cells.
If you need automatic separators, consider TEXTJOIN.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> CONCAT Function Data
