Sometimes your data is split across multiple columns, but you need to combine it into a single value.
For example:
First Name + Last Name โ Full Name
City + State โ Location
Product Code + Year โ Complete Code
That's where CONCAT() and TEXTJOIN() are useful.
๐ 1. CONCAT() Function
CONCAT() combines text from multiple cells or text values into one string.
Syntax:
=CONCAT(text1, [text2], ...)
Example:
First Name Last Name
Rahul Sharma
Formula:
=CONCAT(A2," ",B2)
Result:
Rahul Sharma
The " " adds a space between the two names.
๐ Example โ Combine Product Information
Product Code Year
Laptop LAP 2026
Formula:
=CONCAT(A2,"-",B2,"-",C2)
Result:
Laptop-LAP-2026
๐ 2. TEXTJOIN() Function
TEXTJOIN() combines multiple text values and allows you to specify a delimiter between them.
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Example:
=TEXTJOIN(", ",TRUE,A2:A5)If the cells contain:
โข SQL
โข Python
โข Excel
โข Power BI
Result:
SQL, Python, Excel, Power BI
๐ What Does TRUE Mean?
The second argument controls whether empty cells should be ignored.
TRUE โ Ignore empty cells
FALSE โ Include empty cells
Example:
=TEXTJOIN(", ",TRUE,A2:A5)This is particularly useful when some cells may be blank.
๐ CONCAT() vs TEXTJOIN()
CONCAT():
โข Combines text.
โข Does not provide a delimiter argument.
โข Useful when you want precise control over separators.
TEXTJOIN():
โข Combines multiple values.
โข Allows you to specify a delimiter.
โข Can automatically ignore empty cells.
โข Great for combining lists.
๐ Real-World Example
Suppose you have:
First Name Last Name City
Rahul Sharma Pune
Create a complete profile:
=TEXTJOIN(" - ",TRUE,A2:C2)Result:
Rahul Sharma - Pune
๐ Common Mistakes
โ Forgetting to add a delimiter when needed.
โ Using FALSE when blank cells should be ignored.
โ Adding unnecessary spaces inside the formula.
๐ Real-World Uses
โข Combine first and last names.
โข Create unique IDs.
โข Combine address components.
โข Build product codes.
โข Create comma-separated lists.
โข Prepare data for reports and dashboards.
โ Quick Tip
Remember:
CONCAT() โ Combine text
TEXTJOIN() โ Combine text + choose a separator + ignore blanks
๐ก For modern Excel, TEXTJOIN() is especially useful when you need to combine an entire range rather than manually joining each cell.
Double Tap โค๏ธ For More