Excel Split Contents of a Cell Into Two Cells

How-to basics · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, select the cell or column and use Data > Text to Columns to split text at a delimiter such as a space or comma. If you need exactly two parts at the first delimiter, formulas such as TEXTBEFORE and TEXTAFTER give you more control and update automatically when the source changes.

Choose a method that fits the data

The best approach depends on whether you are splitting a one-time list or maintaining a workbook that will receive new or edited values. Text to Columns is a quick way to separate a whole column of existing data. Formulas are useful when the source needs to remain intact and the results should recalculate. Flash Fill can recognize a pattern from examples, while Power Query is a good fit for a repeatable cleanup process.

First identify what marks the boundary between the two pieces. Common delimiters include a space in a full name, a comma in a city-and-state value, or a hyphen in a product code. A delimiter-based split works best when the same character separates the parts consistently. If the boundary is a fixed position instead—for example, the first three characters are always a code—use a fixed-width option or formulas based on character positions.

  • For a quick, one-time split of a range, use Text to Columns.
  • For a split that updates when source text changes, use formulas.
  • For a pattern that is easy to demonstrate but awkward to describe, try Flash Fill.
  • For repeatable imports and transformations, consider Power Query.

Split a column with Text to Columns

Text to Columns separates the selected cells into neighboring columns. It does not insert a new column automatically, so check that the destination cells are empty before starting; otherwise, the split output can overwrite existing data. Make a copy of the source column if you want to preserve the original values.

  1. Select the cells to split. You can select a single column or a range of rows containing the same kind of text.
  2. Choose Data > Text to Columns.
  3. In the wizard, choose Delimited when a character separates the pieces. Choose Fixed width when the split position is consistent. Select Next.
  4. For a delimited split, check the relevant delimiter, such as Space, Comma, or Other, and enter a character if needed. Review the preview to confirm where Excel will split the text.
  5. Select Next if you want to set column formats, or choose Finish. Set a destination if the output should go somewhere other than the default cells beside the selection.

For example, splitting Portland,OR at the comma produces Portland in one column and OR in the next. A space delimiter applied to Ada Lovelace produces two columns, but a value such as Mary Ann Smith can produce three. Text to Columns separates at each matching delimiter; it is not limited to the first one.

The wizard also offers controls for cases such as consecutive delimiters and text qualifiers. Inspect the preview carefully, especially when the source includes quoted text or multiple spaces. In Excel for Mac, look for Text to Columns on the Data tab as well; menu presentation can differ slightly. The ribbon route is preferable to relying on a keyboard shortcut that may vary by platform.

Use formulas to split at the first delimiter

Formulas are usually the clearest option when you always want the text before the first separator in one cell and everything after it in another. In Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER handle this directly. Enter the first formula in B2 and the second in C2, then fill both down for the other rows.

With the full text in A2 and the two results intended for B2 and C2, enter this formula in B2. For the example Ada Lovelace, it returns Ada.

=TEXTBEFORE(A2," ")

With the same source in A2, enter this formula in C2. For Ada Lovelace, it returns Lovelace.

=TEXTAFTER(A2," ")

Replace the space inside the quotation marks with the delimiter in your data. For a value such as Portland,OR, use a comma instead. These formulas split at the first matching delimiter: TEXTBEFORE returns the part before it, and TEXTAFTER returns the remainder after it. That is useful for a name with a middle name when the intended result is the first word and all remaining words. Both functions are available in Microsoft 365 and Excel 2024, but not in older perpetual versions such as Excel 2021.

Formula results are calculated values, not a one-time rewrite of the source. If A2 changes, B2 and C2 recalculate. Keep the source column if you need an auditable original, and avoid typing other data into cells where formula results need to spill or be filled down.

Options for older Excel versions

Older versions without TEXTBEFORE and TEXTAFTER can use LEFT, FIND, MID, and LEN. These functions are widely supported. The formulas below assume A2 contains Ada Lovelace, B2 is the first output cell, and C2 is the second output cell. The first formula returns Ada.

=LEFT(A2,FIND(" ",A2)-1)

With the same cell layout, the following formula returns Lovelace in C2.

=MID(A2,FIND(" ",A2)+1,LEN(A2))

These examples expect a space in every source cell. If there is no space, FIND returns an error; if there are extra spaces, the result may include them. You can clean irregular data first or add error handling, but do not wrap a formula in IFERROR without deciding what a missing delimiter should mean. Returning a blank may hide an input that needs review.

Try Flash Fill for a recognizable pattern

Flash Fill can infer a transformation from examples you type. Suppose A2 contains Ada Lovelace. Type Ada in B2, then begin typing the corresponding first name from the next row. If Excel recognizes the pattern, it previews suggested results; accept them with Enter. Use the same approach in another output column for the remaining part of each value.

You can also choose Data > Flash Fill. On Windows, Ctrl+E is the keyboard shortcut. If the suggestions are wrong or incomplete, provide more examples with varied input or use a formula instead. Flash Fill writes values rather than maintaining a live formula relationship, so editing the original text later will not automatically update the extracted result. Review the full output before relying on it.

Use Power Query for repeatable splits

Power Query is useful when you regularly import a file and need to repeat the same cleanup. In Excel for Microsoft 365 on Windows, select a cell in the source data and choose Data > From Table/Range. If the range is not already an Excel table, Excel prompts you to create one. In Power Query Editor, select the column, then choose Transform > Split Column > By Delimiter. Set the delimiter and review the split option before confirming.

When the transformation is ready, choose Home > Close & Load to return the results to Excel. On a later refresh, Power Query can apply the saved steps to updated source data. This is different from a worksheet formula: the query output refreshes when you refresh the query, rather than recalculating immediately after an individual source cell is edited. Power Query is more setup than Text to Columns, but it can save repeated manual work on recurring imports.

Common problems and how to avoid them

  • Output overwrites existing cells: Before using Text to Columns, make sure the columns to the right are empty or choose a separate destination in the wizard. Save a copy before transforming an important dataset.
  • More than two output columns appear: A delimiter-based Text to Columns split acts on each occurrence. For exactly two parts at the first separator, use TEXTBEFORE and TEXTAFTER or split only at the first occurrence in Power Query.
  • Names or phrases split unexpectedly: A space is not always a reliable boundary. Multiple spaces, middle names, compound surnames, and inconsistent punctuation can produce different results. Decide the rule for ambiguous cases before applying it to a large list.
  • Numbers or dates change appearance: Excel may interpret split text as numbers or dates. In the Text to Columns wizard, set a column’s data format to Text when leading zeros or exact characters matter. For example, a code such as 0074 should generally remain text if its zeros are meaningful.
  • Formulas show an error: Check whether every row contains the delimiter exactly as written in the formula. A regular space, a comma, and a nonbreaking space are different characters. Clean or standardize inconsistent source text before splitting.

Frequently Asked Questions

How do I split one Excel cell into exactly two cells?
Use TEXTBEFORE and TEXTAFTER with the delimiter if the split should happen at its first occurrence. For example, for text in A2 separated by a space, put =TEXTBEFORE(A2," ") in one cell and =TEXTAFTER(A2," ") in another. Text to Columns can create more than two outputs if the delimiter appears several times.

Can Excel split a cell at a comma?
Yes. In Text to Columns, select the Comma delimiter. In a formula, use a comma as the quoted delimiter, as in =TEXTBEFORE(A2,",") for the part before the first comma. Check your data first if commas also appear within quoted phrases.

Can I split a cell without changing the original?
Yes. Put formulas in other cells, or set a separate destination in the Text to Columns wizard. Keeping a copy of the source is also wise when the split is a one-time transformation, because Text to Columns writes its results into worksheet cells.

Why did Text to Columns create more than two columns?
It split at each occurrence of the selected delimiter. A space-separated value with three words can therefore create three results. Use formulas that split at the first delimiter when you want the first word separated from everything after it.

Will a formula update if I edit the original cell?
Yes. A formula referring to the source cell recalculates when that cell changes. Text to Columns and Flash Fill produce values instead, so their results do not automatically stay synchronized with later edits to the original.

What if the cell has no delimiter?
TEXTBEFORE and TEXTAFTER return an error by default when the requested delimiter is missing, and older formulas using FIND also return an error. Decide whether such rows should remain blank, stay unchanged, or be flagged for correction, then handle that case deliberately.

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.