Excel Split Empty Cells Into Multiple Rows?

How-to basics · Updated November 21, 2026

No—an empty Excel cell contains no values to split into multiple rows. If you mean splitting delimited text, such as red,blue,green, into separate rows, use Power Query’s Split into Rows option or a Microsoft 365 formula; if you need several blank rows, insert or generate rows rather than splitting the empty cell.

What “split an empty cell” can mean

The phrase can describe two different tasks. One is taking a cell that contains a list and putting each item on its own row. The other is starting with a blank cell and asking Excel to turn it into several blank rows. Excel can do the first when there is text and a delimiter to divide it by. It cannot extract multiple values from a cell that has no content.

A cell can look empty for more than one reason. It may truly contain nothing, contain a formula that returns an empty string, or contain spaces that are hard to see. Those cases can behave differently in formulas and data-cleaning tools. Before choosing a method, check whether there is actually text to split and decide what should happen to empty entries between delimiters.

For example, a cell containing red,blue,green has three values and two comma separators. A cell containing red,,green has an empty value between two commas. A genuinely blank cell has neither values nor separators. Excel cannot infer how many rows the blank cell is supposed to produce.

Choose the right approach for the job

For a one-time transformation or a repeatable import, Power Query is usually the clearest choice. It can split a delimited column into rows and refresh the result when the source data changes. For a live worksheet solution in Microsoft 365, TEXTSPLIT can return items as a spilling array. If you only want blank space in a sheet, add rows directly or use a separate list that defines how many rows to create.

  • Text exists and should become rows: use Power Query for a table or TEXTSPLIT for a formula-driven result.
  • The cell is blank and you want blank rows: insert rows, or create a structured output based on another column that specifies the required row count.
  • The cell has a formula returning an empty string: decide whether that should be treated as missing data or as an empty list item.
  • There are consecutive delimiters: choose whether empty items should be retained or ignored before splitting.

Do not confuse splitting into rows with Text to Columns. Text to Columns divides text across columns, which is useful for separating a first and last name horizontally. To create a vertical list, use Power Query or a formula that returns a vertical array.

Split delimited text into rows with Power Query

Power Query is useful when the source is a table, the transformation needs to be repeated, or the result should be refreshed after the source changes. Start with a column that contains the delimited values. Each source record can then become multiple output records, with the other columns repeated alongside the split values.

  1. Arrange the source data in columns with headers. Select a cell in the range and choose Data > From Table/Range. If Excel asks whether to create a table, confirm the range and headers.
  2. In the Power Query Editor, select the column containing the delimited text.
  3. Choose Home > Split Column > By Delimiter. You can also find the split command on the Transform tab.
  4. Choose the delimiter, such as a comma, semicolon, or space. Check the preview to ensure Excel has identified the intended separator.
  5. Open Advanced options and select Split into Rows, rather than splitting into columns. Confirm the dialog.
  6. Review the result, then choose Home > Close & Load to return the transformed data to Excel.

For example, suppose a table has an Order column and a Tags column. One row contains order 104 with tags urgent,fragile. Splitting Tags into rows creates two output rows: order 104 with urgent, and order 104 with fragile. The Order value is repeated so each tag remains associated with its source record.

A blank or null source value does not provide multiple pieces to expand. Power Query will not guess a number of rows based on a blank; the result remains blank/null or is handled according to the query steps and the source’s representation of the blank. If a blank record should instead produce a specific number of output rows, add that rule explicitly in the source or query.

Use TEXTSPLIT for a formula-driven result

In Microsoft 365 and Excel 2024, TEXTSPLIT can divide text by a column delimiter or a row delimiter. To spill items vertically, provide the row delimiter as the third argument and leave the column delimiter argument empty. The formula goes in the first output cell, and Excel spills the results into the cells below. Keep those cells clear or Excel may show a #SPILL! error.

With the text in A2 as red,blue,green, enter this in B2 to split at each comma into rows:

With the text in A2 and the first output cell B2, the formula returns red, blue, and green in B2:B4:

=TEXTSPLIT(A2,,",")

By default, TEXTSPLIT preserves empty items between delimiters. With red,,green in A2, the vertical result includes red, a blank item, and green. To discard empty items, set the optional ignore_empty argument to TRUE. With the same A2 input and B2 as the output cell, this formula returns red and green in B2:B3:

=TEXTSPLIT(A2,,",",TRUE)

These formulas do not turn a truly empty cell into several blank rows. They can return an empty result or an error depending on the input and Excel version, so handle empty source cells deliberately when building a larger formula. One simple option is to use an IF test so a blank input displays as blank rather than attempting the split. With source text in A2 and the formula entered in B2, this returns a blank display when A2 is empty, and otherwise spills the comma-separated items vertically:

=IF(A2="","",TEXTSPLIT(A2,,","))

This test also treats a formula result of "" as empty. It does not treat a cell containing spaces as empty; those spaces are characters. If spaces should be ignored, clean the source data or apply an appropriate text-cleaning step before splitting. TEXTSPLIT is available in Microsoft 365 and Excel 2024, but not in older perpetual editions such as Excel 2019.

Handle blanks and empty items carefully

When a list contains repeated separators, the empty item may carry meaning. For example, North,,South might indicate a missing middle entry in a fixed sequence. Keeping that blank preserves the position of South as the third item. In other data, such as a casually typed tag list, the extra separator may be accidental, and ignoring empty items is more useful.

Also distinguish an empty item from whitespace. A value such as red, ,green contains a space between the delimiters, not a truly empty string. A split may therefore produce a result that looks blank but is not empty. Trimming spaces or cleaning the source before splitting helps prevent confusing output. In Power Query, text-cleaning transformations can be applied before or after the split; in a worksheet formula, extra cleanup may be needed depending on the data.

For Power Query, an empty cell might arrive as a null value, while a formula-generated empty string may be represented differently. That distinction can matter in later filters, replacements, and counts. Inspect the preview and the loaded result rather than assuming every visually blank cell has identical underlying data.

When you need blank rows instead

If the goal is simply to create room for future entries, select the row numbers where space is needed, right-click, and choose Insert. To insert several rows, select the same number of existing row headers first. Excel inserts that many rows above the selection. On Windows, Ctrl+Shift++ opens the Insert dialog after selecting rows; on Mac, use Control+Shift+= in many keyboard layouts, or use the row-header context menu if the shortcut differs on your keyboard.

If you want an empty source record to generate a fixed number of blank output rows, that count must come from a rule elsewhere—for example, a separate quantity field or a defined template. An empty cell alone cannot tell Excel whether to create one, three, or ten rows. A formula such as TEXTSPLIT is not a row-insertion command: it returns values in a spill range and does not physically add worksheet rows or shift existing data.

For repeated operational work, avoid manually inserting rows into a source table if the real task is to expand a list. Keep the original data intact and create a separate Power Query output. This makes the transformation easier to refresh and reduces the risk of overwriting neighboring records.

Common problems and how to fix them

  • The result spills across columns: the delimiter was supplied as the column delimiter. For a vertical TEXTSPLIT result, place the separator in the row_delimiter argument.
  • Power Query creates columns instead of rows: return to Split Column by Delimiter, open Advanced options, and select Split into Rows.
  • You see #SPILL!: clear any occupied cells in the intended spill area, including cells that appear empty but contain spaces or formulas.
  • Blank-looking entries remain: check for spaces or formulas returning an empty string. Clean or normalize those values before deciding how to treat them.
  • The formula name is not recognized: TEXTSPLIT requires Microsoft 365 or Excel 2024. In older Excel versions, use Power Query if available or a carefully designed alternative.
  • The output does not update as expected: formula results recalculate with their source, while Power Query output generally needs a refresh. Use Data > Refresh All to refresh workbook queries.

Check the expected row count before replacing or deleting the source. If each non-empty list item should become one output row, compare the output with the number of items in the input lists. Pay particular attention to consecutive delimiters, because retaining or discarding empty items changes that count.

Frequently Asked Questions

Can Excel split a blank cell into two rows?

No. A blank cell contains no separator or values, so Excel cannot determine what two rows should contain. Insert two rows manually or define a separate rule that specifies the required number of rows.

How do I split comma-separated text into rows in Excel?

Use Power Query’s Split Column by Delimiter command and choose Split into Rows under Advanced options. In Microsoft 365 or Excel 2024, TEXTSPLIT can also return a vertical spill by using the comma as its row delimiter.

Can Text to Columns split text into rows?

No. Text to Columns separates values across columns. For a vertical result, use Power Query’s Split into Rows option or a formula such as TEXTSPLIT configured with a row delimiter.

How do I keep blank items between commas?

TEXTSPLIT keeps empty items by default when delimiters appear consecutively. Do not set ignore_empty to TRUE if those positions matter. In Power Query, inspect how empty values appear in the preview and preserve them if they are meaningful to the data.

Does splitting text insert worksheet rows?

Power Query creates a transformed output with more records; it does not insert rows into the middle of the original source. TEXTSPLIT creates a spill range and does not move existing cells. Load or place the result in a separate area with enough room.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.