Full names in one cell. Addresses. Product codes imported from a database. The fix everyone reaches for is Excel’s Text to Columns or Google Sheets’ Split text to columns, and about half the time it goes wrong on the first try. The values land in the wrong columns, codes lose their leading zeros, dates come out sideways, or the split quietly wipes data sitting next to your column.

Good news: every one of these failures has a known cause and a tested fix. Here are the seven ways splitting breaks, and what to do for each.

Before the list, one difference matters: Excel’s version is a wizard (Data > Text to Columns) where you control the delimiter and column formats. Google Sheets (Data > Split text to columns) has no wizard; it auto-detects or uses a custom separator you pick from the menu. The fixes below map to both.

Find your fix in 30 seconds:

What you see What’s behind it Fix
Button is grayed out More than one column selected #1
Everything lands in one column Wrong or invisible delimiter #2
Values shift left, columns misalign Empty fields merged together #3
00123 turns into 123 Codes stored as numbers #4
Dates come out as 46023 or wrong order Locale/date-order mismatch #5
Data next to the column disappears Split overwrote it #6
Nothing happens at all after Finish Web Excel, hidden spaces, fixed-width data, protected sheet #7

1. The Text to Columns button is grayed out

Why: You selected more than one column. Text to Columns only works on one column at a time, so Excel disables the button the moment your selection spans two or more columns.

The fix: Click into a single cell of the column you want (or select exactly one column), and the button activates. If you need to split several columns with the same rule, split them one at a time.

2. Everything splits into the wrong columns

Why: The delimiter is wrong. Excel’s wizard defaults to Tab: paste comma-separated data and it happily puts the whole row in one column. Google Sheets guesses the separator, and it guesses wrong often enough to be annoying. CSVs exported in Europe use semicolons; US exports use commas.

The fix: Tell the tool which character separates your fields.

  • Excel: Data > Text to Columns, choose Delimited, and pick Comma (or tick Other and type ;). The preview at the bottom of the wizard shows the split before you commit, so if it looks wrong there, fix it there.
  • Google Sheets: Data > Split text to columns, choose Custom, and type the separator.

3. Columns shift because empty fields get skipped

Why: Your CSV has rows with empty fields, like John,,London. Excel’s “Treat consecutive delimiters as one” checkbox is on by default, so a,,b becomes two values instead of three, and every value after the gap slides one column to the left. Rows with different numbers of empties then misalign against each other.

The fix: Open the wizard, keep Delimited, and untick “Treat consecutive delimiters as one.” Empty fields stay empty and columns stay aligned. If you’re splitting with a formula instead (see below), remember Google Sheets’ SPLIT also skips empty text by default, so add FALSE as the fourth argument (=SPLIT(A2, ",", FALSE, FALSE)) to keep them.

4. Leading zeros disappear (00123 becomes 123)

Why: The split wrote your codes as numbers. Spreadsheets drop leading zeros the moment something is treated as a value, and if the original import already converted the data, the zeros were lost before you even split.

The fix: Force the column to text before the conversion happens.

  • Excel wizard: Step 3 of Text to Columns lets you click each column in the preview and set its format; set the code column to Text.
  • Google Sheets: Format the target column as Format > Number > Plain text first, then split.
  • Best of all: fix it at import. For CSVs, use Data > From Text/CSV in Excel or File > Import in Sheets and set the code column’s type to Text there. That prevents the loss instead of repairing it.

Already split and the zeros are gone? Undo (Ctrl+Z), format the column as text, and split again. If you still have the original column, the data isn’t lost yet.

5. Dates come out scrambled or as serial numbers

Why: The tool interprets 01/02/2026 using your sheet’s locale. US Excel reads it as January 2; a UK locale reads February 1. And an Excel date shown as 46023 isn’t broken: it’s a date serial in the wrong display format, which confuses everyone at least once.

The fix: In the Excel wizard, step 3 has a Date column format where you choose the order (MDY or DMY) that matches your source data. In Google Sheets there’s no wizard: if the split mangles dates, undo, set the destination to Plain text, split, and only then convert with DATEVALUE() where a real date is actually needed; it gives you control over which values become dates instead of letting auto-detection decide.

6. Data to the right of your column gets wiped

Why: Both tools write the split results into the columns immediately to the right of the column you selected. If those cells already contain anything (notes, formulas, a second table), the split replaces them. Excel warns you; Google Sheets just writes over them.

The fix: Never split into occupied territory. Copy the column you’re splitting into an empty scratch column (or clear the area to the right), run the split there, and move the results where they belong. Keep the original column until you’ve checked every result. A split is a one-way operation once you save.

7. Nothing happens when you click Finish

Why: The wizard ran, the preview looked right, you clicked Finish, and the column is exactly as it was. When both tools appear to ignore the split, it’s almost always one of four things.

The fix, by cause:

  • You’re in Excel for the web. Text to Columns is a desktop-only feature; the browser version doesn’t have it and no setting turns it on. Open the workbook in desktop Excel, or split it in Google Sheets instead: Data > Split text to columns handles the same data in the browser.
  • Your “spaces” aren’t really spaces. Text copied from a web page or a PDF usually uses non-breaking spaces (CHAR(160)). They look identical to a normal space but aren’t one, so splitting on a space finds nothing at all. Test it with =FIND(CHAR(160),A2), then fix it in a helper column: =SUBSTITUTE(A2,CHAR(160)," "), paste that as values, and split the result.
  • The data is fixed-width, not delimited. If the columns line up through padding rather than being separated by a character, pick Fixed width in the wizard and drag the break lines in the preview. Delimited mode will find nothing.
  • The sheet is protected, or the file is read-only. Excel can’t write the results, so the split silently goes nowhere. Unprotect via Review > Unprotect Sheet, or save a local copy (files opened straight from mail or Teams often open read-only) and split that one.

One more cause that looks like a total failure: while you’re editing a cell (cursor blinking inside it), Excel greys out the Data tab. Press Esc first, then run the wizard.

The split that updates itself (formula alternative)

Text to Columns and Split text to columns are one-time operations: the source is gone, and if the source data changes, you split again from scratch. When you’d rather keep the original and get a live result, use a formula:

  • Google Sheets: =SPLIT(TRIM(A2), " ") in an empty cell spills the pieces into the columns to its right. Wrap the source in TRIM first so double spaces don’t create empty cells.
  • Excel 365: =TEXTSPLIT(A2, ",") spills the same way. It’s the modern replacement for the wizard and recalculates when A2 changes.

Two notes: the formula needs empty cells to its right (it will error if they’re occupied), and once you delete the source column, the split results vanish too, so copy the formula range and paste as values when you’re done.

Which fix is yours?

  • Button grayed out? Select one column only (#1)
  • Everything in one column? Wrong delimiter (#2)
  • Values shifted left? Empty fields merged (#3)
  • Codes lost zeros? Convert to text before splitting (#4)
  • Dates look wrong? Check locale/order, or keep them text (#5)
  • Data missing next to the split? The split overwrote it (#6)
  • Nothing happens at all? Web Excel, hidden CHAR(160) spaces, fixed-width data, or a protected sheet (#7)

Dirty, inconsistently split data is the same root cause behind most formula failures: it’s why a VLOOKUP returns #N/A even when the value “is clearly there,” and it’s why SUMIF totals come out short. Once your columns are clean, keep them that way: a data validation dropdown stops bad entries at the keyboard instead of after the fact.

Split first, verify second, and when in doubt, split a copy.

FAQ

Why is Text to Columns grayed out in Excel? You have more than one column selected. Text to Columns only works on a single column at a time. Click into any one cell in the column you want to split (or select that column alone) and the button becomes active again.

How do I keep leading zeros when splitting text to columns? Make the destination text before you split. In Excel, set the target column to Text in Format Cells, or in the wizard’s step 3 click the column and choose Text. In Google Sheets, set the column to Format > Number > Plain text before Data > Split text to columns. Codes like 00123 then stay text instead of becoming 123. For CSVs, set the column type during import; that’s the most reliable point.

Why is my data splitting into the wrong columns? Almost always the delimiter. Excel defaults to Tab, Sheets auto-detects, and CSVs exported from European systems often use semicolons. Pick the delimiter explicitly: in Excel choose Delimited and type the character under Other, in Sheets choose Split > Custom separator. If empty fields are being skipped, also check Excel’s “Treat consecutive delimiters as one” option.

Does Text to Columns overwrite data next to the column I split? Yes. Both tools write the results into the columns to the right of the one you split, and anything already sitting there gets replaced. If data lives to the right, copy the values you’re splitting to an empty scratch column first, split there, then move the results back.

Can I split text to columns without losing the original data? Yes. Keep the source column and split a copy, or use a formula instead: SPLIT in Google Sheets or TEXTSPLIT in Excel 365 writes results to the right of the formula cell and leaves the source untouched, and it re-splits automatically whenever the source changes.

Why does nothing happen when I click Finish? Check these four, in order: you’re working in Excel for the web (no Text to Columns there — use desktop Excel or Google Sheets); your separators are non-breaking spaces copied from a web page, which don’t split on a normal space (test with =FIND(CHAR(160),A2)); the data is fixed-width and needs the Fixed width option; or the sheet is protected or the file read-only, so nothing can be written. Excel also greys out the Data tab while you’re editing a cell — press Esc first.

Sources