To concatenate in Excel, use the CONCATENATE() function or & operator to merge the contents of two or more cells into one. 💡 For example, =CONCATENATE(A1, " ", B1) will join the values in A1 and B1 with a space in between. This is perfect for creating full names, email addresses, IDs, or any custom text values directly within a spreadsheet. 📋 Whether you’re on Windows, macOS, or Ubuntu, you can speed things up with formula shortcuts and smart techniques.
🔍 What Is CONCATENATE in Excel?
The CONCATENATE function allows you to join multiple text strings or cell values into a single cell. It’s commonly used to combine names, dates, labels, and more.
🧪 Basic Syntax of CONCATENATE
=CONCATENATE(text1, [text2], ...)
text1,text2, etc., can be cell references (A1,B2), literal text ("Hello "), or numbers.
💡 Alternative: Use the & operator
=A1 & " " & B1
⚡ Example CONCATENATE Formulas
| Purpose | Formula | Result |
|---|---|---|
| Full name | =CONCATENATE(A2, " ", B2) | John Doe |
| Email creation | =A2 & "." & B2 & "@domain.com" | john.doe@domain.com |
| Custom ID | ="ID-" & A2 & "-" & B2 | ID-001-JD |
| Date formatting | =TEXT(A2, "dd/mm/yyyy") & " - " & B2 | 01/05/2024 – Delivered |
✨ CONCAT vs CONCATENATE vs TEXTJOIN
| Function | Available In | Description |
|---|---|---|
CONCATENATE | All versions | Joins values (older method) |
CONCAT | Excel 2016+ | Modern alternative to CONCATENATE |
TEXTJOIN | Excel 2016+ | Joins with delimiters (like commas, spaces), can ignore blanks |
✅ Use TEXTJOIN for more advanced merging, especially when working with arrays or skipping empty cells.
🔧 Keyboard Shortcuts for CONCATENATE
| Action | Windows | macOS | Ubuntu (LibreOffice Calc) |
|---|---|---|---|
| Start a formula | = | = | = |
| Insert function dialog | Shift + F3 | Shift + F3 | Ctrl + F2 |
| Enter/accept formula | Enter | Return | Enter |
| Expand formula bar | Ctrl + Shift + U | Cmd + Shift + U | Ctrl + Shift + U |
💡 After typing =CONCATENATE(, press Ctrl + A to open the function helper.
🧠 Real-World Use Cases for CONCATENATE
| Scenario | Formula | Result |
|---|---|---|
| Combine First and Last Names | =A2 & " " & B2 | Emma Stone |
| Product SKU Generation | =LEFT(C2,3) & "-" & D2 | PRO-00123 |
| Mailing Labels | =A2 & CHAR(10) & B2 & ", " & C2 | Multiline address |
| Creating CSV-ready text | =A2 & "," & B2 & "," & C2 | Apple,Red,1.50 |
To insert a line break within a cell:
=A2 & CHAR(10) & B2
Make sure Wrap Text is enabled!
📌 TEXTJOIN: The Better Alternative for Lists
=TEXTJOIN(", ", TRUE, A2:C2)
- Joins all values in A2 to C2, separated by a comma
- Ignores empty cells (with TRUE as the second argument)
🔝 Great for creating lists or merging variable-length data ranges!
🐧 CONCATENATE in Ubuntu/Linux (LibreOffice Calc)
LibreOffice Calc supports CONCATENATE, TEXTJOIN, and &.
Examples:
=CONCATENATE(A1, " ", B1)
=A1 & " - " & B1
=TEXTJOIN(", ", 1, A1:C1)
Shortcut to Insert Function: Ctrl + F2
Function categories:
&: Text concatenationTEXTJOIN: Found in Spreadsheet Functions > Text
🛑 Common Mistakes with CONCATENATE
- ❌ Forgetting to add spaces or punctuation (
=A1 & B1returns “JohnDoe”) - ❌ Using
+instead of&—it won’t work for text - ❌ Not wrapping text in quotes:
" " - ❌ Using
TEXTJOINin Excel versions that don’t support it - ❌ Not enabling Wrap Text when using
CHAR(10)for line breaks
✅ Use TEXT() for formatting dates or numbers before merging.
FAQs
What is the CONCATENATE function used for in Excel?
It merges two or more text strings or cell contents into one cell.
What’s the difference between CONCATENATE and &?
Functionally, they do the same thing. & is shorter and more flexible.
Can I use CONCATENATE for numbers and text?
Yes! Use TEXT() to format numbers or dates properly.
What replaced CONCATENATE in Excel 2016+?
The CONCAT and TEXTJOIN functions are newer, more powerful alternatives.
Does LibreOffice support CONCATENATE?
Yes, it supports CONCATENATE, TEXTJOIN, and &.
Can I add a line break with CONCATENATE?
Yes! Use CHAR(10) and enable Wrap Text in the cell.
✅ Final Thoughts on CONCATENATE in Excel
The CONCATENATE function in Excel is an essential tool for merging text, values, and labels into single cells. 🔗 Whether you’re creating full names, auto-generating IDs, or building clean outputs for reports or exports, mastering text merging will save you time and improve your spreadsheet quality.
As you move forward, don’t forget to explore TEXTJOIN and CONCAT for even more flexibility—because clean data is powerful data! 💪📊
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment