How to Split, Extract or Clean Cell Text in Excel and Google Sheets

Excel doesn’t let you split one unmerged cell into two. What you can do is unmerge a merged cell with Home > Merge & Center > Unmerge Cells, or split a cell’s text into separate columns with Data > Text to Columns. Flash Fill is another quick way to separate text like first and last names, and the TEXTSPLIT formula does the same job in Excel for Microsoft 365 and Excel for the web.

This guide covers Excel for Microsoft 365, Excel 2024, 2021, 2019, and 2016 on Windows. It explains why a cell can’t be cut in half, then walks through every practical way to split cell contents, plus how to make one cell look split.

Why You Can’t Split a Single Cell

Microsoft explains that in Excel a cell is the smallest unit of data, fixed at one row and one column, so it can’t be split the way a Word table cell can. Instead, you move the contents into neighboring cells, or add rows and columns to hold the extra data.

Method 1: Unmerge a Merged Cell

If a cell spans several columns because it was merged, you can split it back into its original cells.

1. Select the merged cell.
2. On the Home tab, select the arrow next to Merge & Center.
3. Select Unmerge Cells. Result: the cell splits back into separate cells, and the contents stay in the top-left one.

Method 2: Split Text Into Columns With Text to Columns

Use this when every cell follows the same pattern, such as names separated by a space or values separated by commas. To do the reverse, see how to combine first and last names in Excel.

1. Insert empty columns to the right of the data, so nothing gets overwritten. Microsoft warns that split results overwrite whatever is in the next cells.
2. Select the cells to split. Text to Columns works on one column at a time.
3. Select Data > Text to Columns. Result: the Convert Text to Columns Wizard opens.
4. Choose Delimited to split at a comma, space, or other character, or Fixed width to split at set positions, and select Next.
5. Pick the delimiter (check Treat consecutive delimiters as one if there are double spaces) and look at the preview. Select Next.
6. Optionally set a column data format and a Destination cell, then select Finish. Result: the text is spread across the columns.

Microsoft notes that Excel for the web doesn’t have the Text to Columns Wizard. There, use the TEXTSPLIT formula below instead. To split into rows rather than columns, split into columns first, then copy the results and use Paste Special > Transpose.

Separate Address Fields Without Losing Postal-Code Zeros

Keep an untouched original address column and work on a copy with enough empty output columns. Use the complete Data > Text to Columns > Delimited wizard above. Choose the separator your records actually use, such as Comma, and inspect the preview across several rows. In the final wizard step, select any postal-code or identifier output column and choose Text to preserve leading zeros, set an empty Destination and select Finish. Compare every field with the original, including multiword streets/cities and unusual postal codes; undo immediately if output overlaps existing data. Splitting on every space divides words and does not reliably identify street, city, region and postal code. A comma inside a street address can also require properly quoted data or a different import method. Review those exceptions rather than assuming one separator produces correct address fields for every country.

Set Fixed-Width Column Breaks in the Excel Wizard

For text arranged in fixed-length fields, preserve the original data and enough empty destination columns. Select the text cells in one column, choose Data > Text to Columns > Fixed width > Next, then click the preview where each column should break. Drag a break to move it or double-click it to remove it. Inspect several records, choose Next, review each output column’s format and an empty Destination, then Finish. Use Text format for identifiers whose leading zeros must remain. Compare the results with the originals and use Undo immediately if the split is wrong. Microsoft’s wizard reference explains these fixed-width preview controls. A delimiter such as a comma needs the separate Delimited option above; fixed width uses positions instead.

Method 3: Use Flash Fill

Flash Fill works well when the pattern is easy to see but not perfectly consistent.

1. In the column next to your data, type how the first result should look, such as the first name only.
2. Press Enter, then press Ctrl + E (or select Data > Flash Fill). Result: Excel fills the rest of the column by following your example.
3. Repeat in the next column for the second part, such as the last name.

Check the results. Flash Fill guesses from your example, so unusual entries like middle names can come out wrong. Unlike a formula, Flash Fill results don’t update if the original data changes.

Method 4: Split Text With a Formula

In Excel for Microsoft 365 and Excel for the web, the TEXTSPLIT function works like Text to Columns in formula form, and it updates automatically.

• =TEXTSPLIT(A2," ") splits the text in A2 at each space into separate columns.
• =TEXTSPLIT(A2,",") splits at commas.
• =TEXTSPLIT(A2,,",") splits at commas down rows instead of across columns, using the row delimiter argument.
• Add TRUE as the fourth argument to ignore empty values from double delimiters, for example =TEXTSPLIT(A2," ",,TRUE).

Make sure the cells to the right (or below) are empty, or the formula shows a #SPILL! error. In older versions such as Excel 2019, combine LEFT, RIGHT, MID, and FIND instead. For example, =LEFT(A2,FIND(" ",A2)-1) returns everything before the first space.

Example: split “Jane Smith” in A2 into first and last names

• First name (Microsoft 365): =TEXTBEFORE(A2," ")
• Last name (Microsoft 365): =TEXTAFTER(A2," ")
• First name (any version): =LEFT(A2,FIND(" ",A2)-1)
• Last name (any version): =RIGHT(A2,LEN(A2)-FIND(" ",A2))

Enter the formulas in B2 and C2, then drag them down the column. These work for names with exactly one space; names with a middle name need a different split.

Method 5: Split a Column With Power Query

For large or repeating imports, Power Query can split a column and repeat the same steps every time you refresh.

1. Select a cell in the data and choose Data > From Table/Range. Result: the Power Query Editor opens.
2. Select the column, then choose Home > Split Column > By Delimiter.
3. Pick the delimiter and where to split, and select OK.
4. Select Close & Load to put the results in a new sheet.

See Microsoft’s Power Query split guide for splitting by number of characters and other options.

Make One Cell Look Split

• Diagonal line: right-click the cell, choose Format Cells > Border, and add a diagonal border. Type one label, press Alt + Enter for a new line, then type the second and adjust the alignment. It’s still one cell. The same Border tab is also where you can make a thicker bottom border in Excel.
• Split a header over two columns: One trick is to insert a column, narrow both columns, and merge the cells above and below, so one row looks divided while others don’t.

If you just need one cell to look bigger, see whether you can resize a single cell in Excel.

Split Names in Google Sheets

On a computer, preserve the original name column and a recoverable spreadsheet copy. Copy the names into a blank working area with enough empty columns to its right for every part of the longest name. Select only the copied name cells, choose Data > Split text to columns, then open the Separator menu and choose Space for space-separated names. Google documents splitting text into columns. Inspect the first, last and multiword names against the untouched originals. Each separator can create another part: middle names and surnames containing spaces do not automatically become a correct two-column First name/Last name list. Label columns only after reviewing those exceptions. Undo an unwanted split immediately. For comma-separated Last name, First name data, choose Comma instead and check the order. The Excel wizard above is a different interface.

Extract Part of a Cell With LEFT, RIGHT or MID

Use an empty helper column beside your source data. If A2 contains ABC123XYZ, type =LEFT(A2,3) to return ABC, =RIGHT(A2,3) to return XYZ, or =MID(A2,4,3) to return 123. Press Enter and fill the chosen formula down the required rows. MID’s start position counts from 1 and its third argument is the number of characters. Microsoft documents MID and lists the related text functions in its data-cleaning guide. Adjust positions and lengths for your actual pattern; these examples do not automatically identify names or arbitrary varying codes.

Compare the helper results against several original cells, including short, blank and unusual values. For a delimiter-based pattern, use Text to Columns or TEXTSPLIT above rather than assuming every row has the same positions. To keep only the resulting text, copy the verified helper cells and use Paste Special > Values in a separate destination. Keep the original column until you have checked the complete output and saved a backup. Extraction creates a result without deleting the original cell contents.

Remove a Known Letter Pattern From Cell Text

For a consistent label, use a helper column and enter =SUBSTITUTE(A2,"kg","") to remove every exact, case-sensitive kg occurrence from A2. To remove three characters at the start of every value, use =REPLACE(A2,1,3,"") instead. Press Enter, fill down and inspect the results before copying them with Paste Special > Values. Microsoft’s SUBSTITUTE guide distinguishes replacing a known string from replacing characters at a position. These examples handle known patterns; they are not a universal way to strip every letter from arbitrary text. SUBSTITUTE can remove an intended pattern in the middle too, and REPLACE removes characters regardless of whether they are letters. Keep original data and test the chosen rule on exceptional rows before saving.

Troubleshooting

• Text to Columns is grayed out: it only works on one column at a time, and not while you’re editing a cell. Press Esc and select a single column of cells.
• Data was overwritten: press Ctrl + Z right away, insert empty columns, and run it again.
• #SPILL! error with TEXTSPLIT: clear the cells where the results need to go.
• Numbers lost leading zeros: in the last step of Text to Columns, set that column’s format to Text.

Frequently Asked Questions

Can I split a cell diagonally?

Not truly. You can add a diagonal border with Format Cells > Border and place text on each side, but it’s still one cell.

Can I split text with a formula?

Yes. In Microsoft 365, =TEXTSPLIT(A2," ") splits the text in A2 at each space.

How do I split first and last names?

Use Text to Columns with a space as the delimiter, or type the first name next to the first entry and press Ctrl + E for Flash Fill.