Raw data almost never arrives in the shape you need it. This lesson teaches you three essential Excel tools — Text to Columns, Flash Fill, and concatenation formulas — so you can split messy data apart or combine fragmented fields together in minutes. Build the data preparation skills every analyst depends on daily.
Picture this: you've just received a CSV export from your company's CRM system. The "Full Name" column contains entries like "Smith, John" and "Johnson, Mary Ellen" — last name first, with a comma separator. Your task is to create a mailing list with a "Dear John" style greeting, which means you need first names in their own column. Or imagine the opposite problem: your database export gave you separate First Name and Last Name columns, but your report template needs a single "Contact" column formatted as "John Smith." Either way, you're staring at a spreadsheet full of data that's in the wrong shape for your needs.
This kind of structural mismatch is one of the most common frustrations in data work. Raw data almost never arrives in exactly the format you need. Sometimes it's too combined — full addresses crammed into a single cell when you need city, state, and zip code separated. Sometimes it's too fragmented — dozens of columns that need to be assembled into a single readable string. Excel gives you three powerful tools to fix both problems: Text to Columns (for splitting), Flash Fill (for intelligent pattern-based transformations), and concatenation (for combining). By the end of this lesson, you'll be able to reshape messy data quickly and confidently.
What you'll learn:
& operator and the CONCAT/TEXTJOIN functionsYou should be comfortable navigating the Excel ribbon and working with basic cell references. If you're new to Excel's interface, the article on Excel Interface Mastery will bring you up to speed quickly. You should also understand what a formula is and how to enter one in a cell. If you're shaky on that, review Essential Excel Functions first.
Before diving into tools, let's understand why this problem exists so frequently. Data is created by many systems — CRMs, ERPs, legacy databases, web forms — each with its own conventions. Some systems store full names in one field because that's simpler to display. Others separate every attribute into its own column because that's better for database querying. When data crosses system boundaries, you inherit those design decisions whether you like them or not.
The good news: Excel's splitting and combining tools are genuinely fast once you know them. What might take an hour of manual copy-paste work can often be done in under two minutes.
Key insight
Always work on a copy of your raw data when splitting or combining. Text to Columns modifies data in place and can't be easily undone if you've already saved. Insert a new sheet or duplicate your column before running any transformation.
Text to Columns is a wizard-based tool that takes content from a single column and distributes it across multiple columns based on a pattern you specify. It works in two modes:
|)Let's say column A contains product codes and category names joined by a pipe character, like this:
A1: PRD-00142|Office Supplies|Pens
A2: PRD-00891|Furniture|Chairs
A3: PRD-02204|Technology|Monitors
You want the product code, category, and subcategory in separate columns.
Step 1: Select the column of data you want to split — in this case, click the column A header to select the entire column, or select just cells A1:A3.
Step 2: Go to the Data tab on the ribbon, then click Text to Columns in the Data Tools group.
Step 3: The Convert Text to Columns Wizard opens. Choose Delimited and click Next.
Step 4: In the Delimiters section, uncheck Tab (which is checked by default) and check Other. Type a pipe character (|) in the text box next to "Other." You'll immediately see a preview at the bottom of the dialog showing how your data will be split. Click Next.
Step 5: Excel shows you a column data format screen. For most text data, General works fine — it will treat numbers as numbers and text as text. If you're splitting dates, you can specify the date format here. Click Finish.
Your data now occupies three columns: A (product code), B (category), C (subcategory). Excel writes the new columns to the right, overwriting whatever was there, which is why that earlier warning about working on a copy matters.
Warning
Text to Columns overwrites existing data in the columns to the right of your selection without asking for confirmation. If you have data in columns B and C already, it will be replaced. Always ensure the destination columns are empty before running the wizard.
Now let's tackle that mailing list problem from the introduction. Column A contains names in "LastName, FirstName" format:
A1: Smith, John
A2: Johnson, Mary Ellen
A3: Garcia, Carlos
Select column A, open Text to Columns, choose Delimited, then check Comma as the delimiter. After clicking Finish, you'll get:
Smith / Johnson / Garcia John / Mary Ellen / CarlosNotice the leading space in column B — that's because the original data had a space after the comma. This is a very common issue. To fix it, also check Space as a delimiter in addition to Comma. But be careful: if you do that with "Mary Ellen," it will split into three columns: Mary and Ellen end up in separate cells.
This is exactly the moment where Text to Columns reaches its limits. Inconsistent data — some people have middle names, some don't — is where Flash Fill becomes the better tool.
Some data has no delimiter at all, but each field always occupies a fixed number of characters. This is common with legacy mainframe exports. For example:
A1: CA 90210Los Angeles
A2: NY 10001New York
A3: TX 78201San Antonio
State is characters 1-2, zip is characters 4-8, city starts at character 9. In the Text to Columns wizard, choose Fixed Width instead of Delimited. A ruler preview appears where you can click to create split lines at the exact character positions you want. Drag split lines to adjust them, and double-click a line to remove it.
Flash Fill is Excel's intelligent autocomplete for data transformation. You show it one or two examples of what you want, and it figures out the pattern and applies it to the rest of the column. It was introduced in Excel 2013 and works entirely without formulas.
Under the hood, Flash Fill uses pattern recognition — it looks at your examples alongside the adjacent source data and infers the transformation rule. It's remarkably good at things like extracting parts of text, reformatting dates, changing capitalization, and combining values.
Back to our name problem. Let's say column A has the messy format:
A1: Smith, John
A2: Johnson, Mary Ellen
A3: Garcia, Carlos
Click into cell B1 and type John — the first name from A1. Press Enter to move to B2. Start typing Mary — and watch what happens. Excel will display a grayed-out suggestion for the rest of the column: Mary Ellen in B2, Carlos in B3. Press Enter to accept, or press Escape to dismiss.
If the suggestion doesn't appear automatically, you can trigger Flash Fill manually: click back into B1, then press Ctrl+E (the keyboard shortcut for Flash Fill), or go to Data tab → Flash Fill.
Tip
Flash Fill doesn't always activate automatically. If you're not seeing the gray preview, try providing two examples instead of one — type the first two values manually, then press Ctrl+E from the third cell.
Flash Fill handles a surprising range of transformations:
Reformatting phone numbers. If column A has 5551234567 and you want (555) 123-4567, type the formatted version in B1 and Flash Fill will handle the rest.
Extracting domains from email addresses. Column A has john.smith@acme.com — type acme.com in B1 and Flash Fill extracts the domain from every row.
Combining and transforming simultaneously. Column A has first names, column B has last names, and you want column C formatted as Smith, J. — type the first example and Flash Fill handles the rest.
Warning
Flash Fill produces static values, not formulas. If you change the source data in column A, the Flash Fill results in column B will NOT update automatically. For data that changes frequently, use formulas instead (covered in the next section).
Flash Fill infers rules from your examples, which means it can infer the wrong rule if your examples are ambiguous. If your first name example happens to be a single-word name like "Carlos" and the next few rows also have single-word first names, Flash Fill might extract the entire entry rather than just the first name.
Always scroll through the filled results before moving on. If the pattern breaks at any row, that's a signal to provide a more representative example, or to switch to a formula-based approach for more reliable control.
Concatenation means joining text strings together into one. In Excel, the simplest way to concatenate is with the ampersand (&) operator. It works like this:
= A2 & B2
If A2 contains John and B2 contains Smith, this formula returns JohnSmith — no space. To add a space between them:
= A2 & " " & B2
Result: John Smith
You can chain as many pieces together as you need. For a full mailing address in one cell:
= A2 & ", " & B2 & ", " & C2 & " " & D2
Where A2 is city, B2 is state, C2 is zip, D2 is country. Result: Austin, TX 78701 USA
The & operator is easy to read and works in all versions of Excel.
Tip
When combining data from different cells, think about what separator makes sense for a human reader. A space works for names. A comma-space works for addresses. A hyphen works for codes like PRD-2024-Q1. Always include the separator as a text string in quotes inside your formula.
Excel 2019 and Microsoft 365 include CONCAT, which is a modernized replacement for the older CONCATENATE function. The syntax is:
= CONCAT(text1, text2, text3, ...)
For example:
= CONCAT(A2, " ", B2)
The main advantage of CONCAT over & is that it can accept ranges. You can write CONCAT(A2:D2) to join all four cells at once — though it won't add any separator between them. For that, you need TEXTJOIN.
TEXTJOIN is the most flexible concatenation function. Its syntax is:
= TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)
To join a row of values with a comma-space separator:
= TEXTJOIN(", ", TRUE, A2:E2)
This is extremely useful when you have a variable number of non-empty fields and don't want ugly double-commas from blank cells in the middle. The ignore_empty argument set to TRUE handles that gracefully.
A real-world use case: you have separate columns for Phone 1, Phone 2, and Phone 3. Some contacts have one phone number, some have three. You want a single "All Phones" column:
= TEXTJOIN(" | ", TRUE, C2, D2, E2)
Result for a contact with two numbers: 555-1234 | 555-5678
Result for a contact with one number: 555-1234 (no trailing pipe)
Key insight
TEXTJOIN is available in Excel 2019, Excel for Microsoft 365, and Excel for the web. If you're on Excel 2016 or earlier, you'll need to use & chains or nested CONCATENATE calls instead. Check the Working with Dates, Times, and Text Functions in Excel article for older-version alternatives.
Raw data often has inconsistent spacing or capitalization, which will survive concatenation and make your output look unprofessional. Two functions to know:
TRIM removes leading and trailing spaces, and collapses multiple internal spaces to single spaces:
= TRIM(A2)
PROPER converts text to Title Case (first letter of each word capitalized):
= PROPER(A2)
Combining them with concatenation:
= TRIM(PROPER(A2)) & " " & TRIM(PROPER(B2))
This formula takes " john " and "SMITH" and produces "John Smith" — clean, professional output.
If you want to go deeper with text manipulation functions like LEFT, RIGHT, MID, and SUBSTITUTE, the article on Mastering Excel's Text Functions is the natural next step after this one.
Here's a practical decision guide:
| Situation | Best Tool |
|---|---|
| Data has a consistent delimiter, one-time split | Text to Columns |
| Transformation is visual/pattern-based, no formula needed | Flash Fill |
| Source data changes and results need to update | Concatenation formula |
| Extracting variable-length substrings | Flash Fill or LEFT/RIGHT/MID formulas |
| Joining many cells with same separator, skipping blanks | TEXTJOIN |
| Simple join of 2-3 cells | & operator |
Work through this exercise from start to finish using a blank Excel workbook.
Setup: Enter the following data in column A (starting at A1):
Full Name & Department
Patel, Ananya | Engineering
Okonkwo, Emeka | Marketing
Chen, Wei-Lin | Data Science
Rosenberg, David | Finance
Task 1 — Text to Columns: Select A2:A5. Use Text to Columns with the pipe (|) delimiter to split the data into two columns. You should get names in column A and department names (with a leading space) in column B.
Task 2 — Clean the department column: In column C, write a formula using TRIM to remove the leading space from column B. Your formula in C2 should be =TRIM(B2), then copy it down to C5.
Task 3 — Flash Fill for first names: In column D, manually type Ananya in D2. Press Enter, then press Ctrl+E to trigger Flash Fill. Verify it correctly extracted Emeka, Wei-Lin, and David.
Task 4 — TEXTJOIN for a summary column: In column E2, write a formula that combines the first name from column D with the department from column C, formatted as "Ananya — Engineering":
= D2 & " — " & C2
Copy down to E5.
Task 5 — Verify: Your final column E should read:
Ananya — EngineeringEmeka — MarketingWei-Lin — Data ScienceDavid — FinanceProblem: Text to Columns produced extra blank columns.
This happens when your data has consecutive delimiters (like two spaces in a row, or ,, in a CSV). Most of the time, checking the "Treat consecutive delimiters as one" checkbox in the wizard fixes it.
Problem: Dates got scrambled after Text to Columns. If Excel misidentifies a column as dates and reformats the values, open Text to Columns again, advance to Step 3, and explicitly set that column's format to "Text" to prevent automatic date parsing.
Problem: Flash Fill filled some cells wrong. Scroll through all filled values — Flash Fill errors often cluster around rows with unusual formatting, extra spaces, or unexpected characters. Fix those source cells first, then re-run Flash Fill on the corrected data.
Problem: My TEXTJOIN formula shows a #NAME? error.
This means your version of Excel doesn't support TEXTJOIN. It's available from Excel 2019 and Microsoft 365 onwards. For older versions, replicate the behavior with nested & operators and IF checks for blank cells.
Problem: Concatenated output has extra spaces in the middle.
Use TRIM wrapped around each cell reference in your formula: =TRIM(A2) & " " & TRIM(B2). This ensures spaces inside the source values don't double up.
Problem: Numbers lost their formatting when concatenated.
When you use & with a number, Excel converts it to a plain number string, dropping currency symbols, decimal formatting, etc. Wrap the number in TEXT to preserve formatting: ="Total: " & TEXT(B2, "$#,##0.00"). For more on formatting numbers, see Formatting Numbers, Dates, and Cells in Excel.
Note
Concatenation formulas that reference cells with formula results will work correctly — you don't need to "paste as values" first. However, if you plan to delete the source columns after combining, you will need to paste the results as values first, or your formulas will break. Learn more about that technique in Mastering Excel's Paste Special.
You now have three complementary tools for reshaping data:
&, CONCAT, or TEXTJOIN) is the right choice when you need results that update dynamically as source data changes, or when you need precise control over formatting.These tools work best in combination. A typical workflow might be: use Text to Columns to do an initial split, use TRIM formulas to clean the results, then concatenate the cleaned fields into a final output column.
Clean, well-shaped data is the prerequisite for everything that comes next — whether you're building PivotTables to summarize your dataset, using VLOOKUP or XLOOKUP to join data across tables, or applying data validation rules to keep future data entry consistent. The time you invest in reshaping data correctly at this stage pays dividends at every downstream step.
For a deeper dive into text manipulation — extracting substrings, finding character positions, and doing sophisticated replacements — the lesson on Mastering Excel's Text Functions picks up exactly where this one leaves off.