Raw data almost never arrives in the shape you need it. This in-depth lesson teaches you how to combine text from multiple columns with CONCATENATE, `&`, and TEXTJOIN — and how to split messy concatenated fields apart with Text-to-Columns — so you can reshape any dataset into the format your analysis actually requires.

You've just received a data export from your CRM. The first name is in column A, last name in column B, city in column C, and state in column D — but your reporting template needs a single "Full Name" column and a "Location" field formatted as "City, ST." Meanwhile, the product codes column looks like SKU-2024-WH-XL and your inventory system needs those four components split into separate fields. Sound familiar?
This is the daily reality of data preparation work. Raw data almost never arrives in the shape you need it. Fields that should be combined are split apart; fields that should be split are jammed together. Before you can run a PivotTable, build a lookup with VLOOKUP or XLOOKUP, or feed data into a dashboard, you need clean, correctly structured source data. That's exactly what this lesson covers.
By the end of this lesson, you'll be able to combine text from multiple columns with precision, handle delimiters and blank cells gracefully, and split messy concatenated strings back into clean separate fields — all without touching a single cell by hand.
What you'll learn:
& operator is often the smarter choiceYou should be comfortable with basic Excel navigation and formula entry. Familiarity with cell references — relative, absolute, and mixed will help you understand why some formulas in this lesson use $ signs. If you've worked with essential Excel functions like IF and COUNTIF, you're at the right level.
Before diving into syntax, it's worth understanding that Excel gives you three distinct ways to combine text, and they're not interchangeable:
& operator — the workhorse for quick, simple joinsUnderstanding when to reach for each one is as important as knowing the syntax. Let's build that understanding from the ground up.
CONCATENATE was Excel's original text-joining function. Its syntax is straightforward:
=CONCATENATE(text1, text2, text3, ...)
You pass it up to 255 arguments, and it stitches them together left to right. Here's a simple example. Suppose you have:
MariaSantos=CONCATENATE(A2, " ", B2)
Result: Maria Santos
The space between the two names is a literal string — you have to supply it yourself. This is the most common beginner mistake: forgetting that CONCATENATE has zero awareness of what your data means. It just glues.
The & operator does exactly the same thing with cleaner syntax:
=A2 & " " & B2
Same result: Maria Santos. Most experienced Excel users prefer & for most tasks because it's shorter, reads more naturally left to right, and is easier to modify. Use CONCATENATE only if you have a strong team convention or if you're maintaining a legacy workbook that already uses it throughout.
Note
Microsoft introduced the CONCAT function in Excel 2019 and Excel 365 as a modernized replacement for CONCATENATE. CONCAT accepts ranges (like CONCAT(A2:D2)) but provides no delimiter control. CONCATENATE does not accept ranges. Neither should be your first choice for complex joins — that's TEXTJOIN's territory.
Let's work with a more realistic dataset. You're cleaning a customer list with these columns:
| A (First) | B (Last) | C (Title) | D (Department) | E (City) | F (State) |
|---|---|---|---|---|---|
| James | Okafor | Sr. Analyst | Finance | Chicago | IL |
| Priya | Nair | Manager | Operations | Austin | TX |
| Derek | Huang | Director | Engineering | Seattle | WA |
Goal 1: Full name for a report header
=B2 & ", " & A2
Result: Okafor, James — last name first, comma-separated, as most formal reports require.
Goal 2: Professional display name with title
=C2 & " " & A2 & " " & B2
Result: Sr. Analyst James Okafor
Goal 3: Location field
=E2 & ", " & F2
Result: Chicago, IL
Goal 4: Email address generation (a classic data prep task)
Suppose the company email format is first.last@company.com and you want to generate draft emails from this list:
=LOWER(A2) & "." & LOWER(B2) & "@company.com"
Result: james.okafor@company.com
Notice the use of LOWER to normalize the case. This kind of combination — text functions nested inside a concatenation — is where & syntax really shines over CONCATENATE. The readability stays high because you're reading a chain of joins from left to right.
Tip
When you're combining numbers or dates with text using &, Excel will sometimes format them unexpectedly. The number 1500 will appear as 1500, but a date like 44927 (Excel's internal date serial) needs to be wrapped in TEXT(): ="Invoice date: " & TEXT(A2,"MM/DD/YYYY"). Always test your combinations when mixing data types.
What if a cell contains a value you want to format before joining? The TEXT() function is your bridge between numeric/date values and text strings:
="Q" & QUARTER_NUMBER & " Revenue: $" & TEXT(C2,"#,##0")
This becomes especially important when building dynamic labels for charts or reporting cells. If you skip TEXT(), you'll get raw serial numbers for dates and unformatted decimals for currency — both of which look terrible in a finished report. This connects closely to the skills covered in formatting numbers, dates, and cells with custom number formats.
Imagine you need to combine not two or three fields, but a variable number of them — say, all the product tags for an item, spread across columns C through K. With &, you'd have to chain eight joins manually. With CONCATENATE, same problem. And what if some of those cells are empty? You'd get double-delimiters: Tag1,,Tag3 with a gap where Tag2 was blank.
TEXTJOIN solves all of this. Its syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Three arguments to understand:
"" for no separator, ", " for comma-space, " | " for pipe-separated, etc.TRUE skips blank cells; FALSE includes them (producing double-delimiters). You'll almost always want TRUE.C2:K2.Scenario 1: Combining product tags
Your product catalog has tags spread across columns:
| A (SKU) | B (Name) | C (Tag1) | D (Tag2) | E (Tag3) | F (Tag4) |
|---|---|---|---|---|---|
| P001 | Laptop Stand | Ergonomic | Home Office | Aluminum | |
| P002 | Desk Lamp | LED | Dimmable | Office |
To build a combined tags field:
=TEXTJOIN(", ", TRUE, C2:F2)
For P001: Ergonomic, Home Office, Aluminum
For P002: LED, Dimmable, Office
Notice that TEXTJOIN skips the empty cells entirely. With & or CONCATENATE, you'd get Ergonomic, Home Office, , Aluminum — an ugly double-comma that you'd then need to clean up with SUBSTITUTE.
Scenario 2: Building a mailing address
Address fields are notoriously messy because some components (suite numbers, attention lines) are optional:
| A | B | C | D | E |
|---|---|---|---|---|
| 450 N Michigan Ave | Suite 800 | Chicago | IL 60611 | |
| 1 Main Street | Portland | OR 97201 |
=TEXTJOIN(", ", TRUE, A2:C2) & ", " & D2 & " " & E2
Row 1: 450 N Michigan Ave, Suite 800, Chicago IL 60611
Row 2: 1 Main Street, Portland OR 97201
The blank Suite field in row 2 is automatically skipped. This is a huge time-saver in real data work.
Warning
TEXTJOIN is only available in Excel 2019, Excel 365, and Excel for the web. If your organization still runs Excel 2016 or earlier, TEXTJOIN won't work. In that case, you'll need to build IFERROR/IF workarounds with &, or consider upgrading. Always check your team's Excel version before deploying TEXTJOIN in a shared workbook.
Scenario 3: Combining values across rows (vertical joins)
TEXTJOIN can also combine down a column, which opens up some powerful aggregation patterns. Suppose you have a list of cities assigned to a sales rep in column B, one per row (B2:B8), and you want a single cell summary:
=TEXTJOIN(" | ", TRUE, B2:B8)
Result: Chicago | Austin | Seattle | Denver | Boston
This is particularly useful for creating summary fields in reporting dashboards or when you need to pass a delimited list to another system. It pairs well with the FILTER function when you want to join only the rows that match a condition:
=TEXTJOIN(", ", TRUE, FILTER(B2:B20, C2:C20="West"))
This joins all city names from rows where the region (column C) is "West" — a genuinely powerful pattern for dynamic reporting.
Key insight
TEXTJOIN with a range argument (C2:K2) is not just a convenience shortcut — it fundamentally changes what's possible. You can reference an entire data range and let Excel handle the variable number of non-empty values automatically. This makes your formulas resilient when columns are added or removed from the source data.
One of TEXTJOIN's less-obvious capabilities is accepting array expressions as arguments. This means you can apply a transformation to every element before joining:
=TEXTJOIN(", ", TRUE, UPPER(C2:F2))
Result: ERGONOMIC, HOME OFFICE, ALUMINUM — every tag uppercased before joining.
Or add a prefix to each element:
=TEXTJOIN(" | ", TRUE, "#" & C2:F2)
Result: #Ergonomic | #Home Office | #Aluminum — hashtag-prefixed tags, useful for social media content or tagging systems.
Tip
When using TEXTJOIN with an array formula like "#" & C2:F2, you may need to confirm with Ctrl+Shift+Enter in older Excel versions (pre-365). In Excel 365, dynamic array support handles this automatically.
Where CONCATENATE and TEXTJOIN combine, Text-to-Columns splits. And just as combining is a core data prep task, so is splitting. You'll encounter it constantly:
"2024-07-15") that Excel won't recognize as datesWH-XL-BLU-2024 that encode four attributesText-to-Columns lives on the Data tab in the ribbon, in the Data Tools group. The button is labeled "Text to Columns." Click it after selecting the column you want to split, and you'll launch a three-step wizard.
Delimited means your data uses a consistent character — comma, tab, pipe, space, semicolon — to separate values. This is the right choice for most real-world scenarios.
Fixed Width means each field always occupies the same number of characters regardless of content. This is common with legacy mainframe exports and some government data formats. You'll see a preview and manually place vertical "break lines" where you want cuts to occur.
For most modern data work, you'll choose Delimited.
After selecting Delimited, you'll see checkboxes for Tab, Semicolon, Comma, Space, and Other. The preview at the bottom updates in real time as you check boxes — watch it carefully.
A few gotchas:
Chicago IL with multiple spaces between words will split into phantom empty columns unless you check this box.|), tilde (~), or any custom delimiter your export system uses."Chicago, North" with the comma inside quotes, setting the text qualifier to " prevents it from splitting at that comma.This step is often glossed over, but it's critically important. For each column in the preview, you can set:
Warning
"General" format sounds safe but can destroy your data. Excel will helpfully convert ZIP codes starting with 0 (like 07030) to the number 7030, stripping the leading zero. It will also convert values like 1-2 or 3/4 to dates. For ID numbers, codes, and anything that looks like it could be a number or date but shouldn't be treated as one, always set the column format to Text.
Scenario: Splitting product codes
Your data has a column of product codes in this format: WH-XL-BLU-2024
The four components are: Warehouse (WH), Size (XL), Color (BLU), Year (2024).
- in the box, click NextYou now have four clean columns from one.
Tip
Text-to-Columns is a one-time operation, not a live formula. If your source data changes, you'll need to run it again. For dynamic, formula-based splitting that updates automatically, use the TEXTSPLIT function (Excel 365 only) or combine LEFT/RIGHT/MID/FIND functions — a topic covered in depth in the lesson on mastering Excel's text functions: LEFT, RIGHT, MID, FIND, and SUBSTITUTE.
One of the most underrated uses of Text-to-Columns is converting text-formatted dates into real Excel date values. When you import data from external systems, dates often come in as text strings that Excel refuses to calculate with.
Scenario: Column A contains dates like "20240715" (YYYYMMDD as text, no delimiters).
Excel converts the text strings to real date serial numbers, which you can then format, calculate with, and sort properly.
The same technique works for dates in formats like 15/07/2024 where Excel is treating them as text because the day comes before the month. Set the format to DMY and Excel figures it out.
Let's put all three tools together in a realistic workflow. You've exported a customer list from a CRM system. Here's what you're working with (simplified to show the structure):
Raw export (everything in column A, comma-delimited):
"Santos,Maria,Sr. Analyst,Finance,Chicago,IL,60601"
"Nair,Priya,Manager,Operations,Austin,TX,78701"
"Huang,Derek,Director,Engineering,Seattle,WA,98101"
Target output needed for the reporting template:
Step 1: Split the raw data with Text-to-Columns
"Step 2: Build the Full Name field
In a new column (let's say column I):
=B2 & " " & A2
Result: Maria Santos
Step 3: Build the Title/Department field
In column J:
=C2 & " — " & D2
Result: Sr. Analyst — Finance
Step 4: Build the Location field
In column K:
=E2 & ", " & F2 & " " & G2
Result: Chicago, IL 60601
Step 5: Handle optional ZIP extension
What if some ZIP codes have the +4 extension (e.g., 60601-1234) and some don't? You'd want to include the extension only when present. That calls for a conditional approach:
=E2 & ", " & F2 & " " & IF(H2<>"", G2 & "-" & H2, G2)
This is the kind of real-world wrinkle that separates production-quality data prep from quick-and-dirty work. The more of your business logic you encode in formulas, the more consistent and auditable your process becomes.
Key insight
The workflow above — split first, then recombine with formulas — is the standard pattern for reshaping data from one format to another. Text-to-Columns gives you clean atomic components; & and TEXTJOIN let you reassemble them in any configuration the target system requires. Think of it as disassembly and reassembly.
Build this yourself using the following dataset. Create a new workbook and enter this data starting in A1:
| A | B | C | D | E |
|---|---|---|---|---|
| Last | First | Phone | Tag1 | Tag2 |
| Okonkwo | Adaeze | 312-555-0192 | Premium | Enterprise |
| Reyes | Carlos | 503-555-0847 | Standard | |
| Vance | Simone | 206-555-3310 | Premium | |
| Bhatt | Rohan | 737-555-0021 | Trial |
Exercise tasks:
In column F, write a formula that creates a "Directory Name" in the format: Last, First (e.g., Okonkwo, Adaeze)
In column G, write a TEXTJOIN formula that creates a combined tags field. It should show only the tags that exist, separated by " / ". For rows with only one tag, it should show just that tag with no trailing separator.
In column H, write a formula that creates a contact record string in the format: Name: Adaeze Okonkwo | Phone: 312-555-0192 | Tags: Premium / Enterprise
Use Text-to-Columns to split the phone numbers in column C into area code, exchange, and subscriber number (Hint: use - as the delimiter, and set all output columns to Text format). Do this in columns I, J, K.
Stretch goal: Write a formula in column L that reconstructs the phone number with parentheses around the area code: (312) 555-0192. Use the results from columns I, J, K and the & operator.
Expected results for row 2 (Okonkwo):
Okonkwo, AdaezePremium / EnterpriseName: Adaeze Okonkwo | Phone: 312-555-0192 | Tags: Premium / Enterprise(312) 555-0192Work through each step before checking against the expected outputs. The H2 formula is the most complex — build it by combining your answers from F2 and G2 as building blocks.
Symptom: Your combined names look like Maria Santos with a double space, or Chicago with a leading space.
Cause: The source cells contain leading or trailing spaces — extremely common in data exported from other systems.
Fix: Wrap each cell reference in TRIM before combining:
=TRIM(A2) & " " & TRIM(B2)
TRIM removes all leading and trailing spaces and reduces internal multiple spaces to a single space.
Symptom: ="Invoice: " & A2 produces Invoice: 45292 instead of Invoice: 01/15/2024.
Cause: Excel converts the date to its underlying serial number when you concatenate it without formatting.
Fix: Use TEXT() with an explicit format:
="Invoice: " & TEXT(A2, "MM/DD/YYYY")
For currency: TEXT(revenue, "$#,##0.00")
Symptom: After running Text-to-Columns, data that was in columns B through E has been replaced.
Cause: Text-to-Columns splits into the columns immediately to the right of the source column by default. If those columns aren't empty, it overwrites them without warning.
Fix: Always move your source column to a blank area of the sheet before running Text-to-Columns, or insert enough empty columns to the right before you start.
Symptom: The formula =TEXTJOIN(", ", TRUE, C2:F2) shows #NAME?.
Cause: You're running Excel 2016 or earlier, which doesn't support TEXTJOIN.
Fix: Use a workaround with IF and CONCATENATE, or better yet, encourage your organization to upgrade. As a bridge solution:
=TRIM(IF(C2<>"",C2&", ","") & IF(D2<>"",D2&", ","") & IF(E2<>"",E2&", ","") & IF(F2<>"",F2,""))
This is ugly and doesn't scale, which is exactly why TEXTJOIN exists. For thorough error handling strategies, see Master Error Handling in Excel: IFERROR, IFNA & Professional Debugging Techniques.
Symptom: ZIP code 07030 becomes 7030, or employee ID 00142 becomes 142.
Cause: You left the column format as "General" in wizard step 3.
Fix: In step 3 of the wizard, click the affected column in the preview pane and set its data format to Text. If you've already run it and lost the zeros, undo (Ctrl+Z), set the destination column to Text format first (right-click > Format Cells > Text), then run Text-to-Columns again.
Symptom: TEXTJOIN returns #VALUE!.
Cause: The combined string exceeds Excel's 32,767 character limit for cell content.
Cause context: This usually happens when joining large ranges. Audit the source data length and consider whether you actually need all of it in a single cell, or whether a PivotTable or summary approach would serve you better.
Tip
When you're unsure why a text formula is misbehaving, use the Evaluate Formula tool (Formulas tab > Formula Auditing group > Evaluate Formula). It steps through your formula one piece at a time, showing intermediate values at each stage. This is invaluable for debugging nested expressions. The full workflow for formula auditing is covered in Mastering Excel Formula Auditing.
You now have a complete toolkit for one of the most common tasks in data work: reshaping text data between formats. Let's consolidate what you've built:
CONCATENATE and & are your tools for direct, formula-driven text joining. Use & for most tasks — it's cleaner. Reserve CONCATENATE for legacy compatibility. Always use TEXT() when mixing non-text values, and TRIM() when your source data might have extra spaces.
TEXTJOIN is the right choice whenever you're joining more than three items, working with ranges, or need automatic handling of blank cells. Its ignore_empty parameter alone is worth the upgrade to Excel 2019+. Its ability to accept array expressions makes it a surprisingly powerful tool for dynamic label generation.
Text-to-Columns handles the inverse operation — splitting. It's not a formula, so it's a one-time transformation, but it's faster and more reliable than formula-based splitting for initial data prep. Watch out for the "General" format trapping leading zeros and misreading codes as dates. Always set sensitive columns to Text explicitly.
The pattern that ties these together: split to atomic components first, then recombine in the shape your analysis needs. This gives you maximum flexibility and keeps your data prep auditable and repeatable.
Where to go next:
Clean, well-structured data isn't a nice-to-have — it's the foundation everything else in Excel sits on. The skills in this lesson are ones you'll use on every serious project you work on.
Excel Fundamentals