How to Split Cells in Excel (Quick Scoop)

You can “split” a cell in Excel in a few different ways: using built‑in tools like Text to Columns and Flash Fill, or with formulas such as LEFT, RIGHT, MID, and TEXTSPLIT (in newer Excel versions). Below is a practical, step‑by‑step guide you could almost read like a short how‑to article.

Fast method: Text to Columns (classic way)

Use this when you have text like “John Smith” in one column and want “John” and “Smith” in separate columns.

Example scenario: Column A has full names, you want first and last names in columns B and C.

1. Select the cells you want to split
   • Click the first cell (e.g., A2), then drag down to the last cell with data.

2. Open Text to Columns
   • Go to the Data tab.
   • Click Text to Columns.

3. Choose splitting mode
   • If your data is separated by characters (space, comma, semicolon, etc.), choose Delimited.
   • If it’s in fixed positions (e.g., first 4 characters are codes), choose Fixed width.

4. Set the delimiter
   • For names like “John Smith”, tick Space.
   • You can also choose Comma , Tab , Semicolon , or type a custom delimiter.

 * For messy data, enable “Treat consecutive delimiters as one”.

5. Choose destination (avoid overwriting!)
   • In the Destination box, choose where the split data should go, like $B$2 so your original data in column A stays intact.

6. Preview, then Finish
   • Check the preview box to confirm it looks right.
   • Click Finish to split the cells into separate columns.

Think of Text to Columns as Excel’s “scissors” for breaking one column of text into several neat pieces.

Newer Excel: TEXTSPLIT, TEXTBEFORE, TEXTAFTER

In recent Microsoft 365 / Excel versions, you get dynamic array functions like TEXTSPLIT , TEXTBEFORE , and TEXTAFTER that split text without wizards.

TEXTSPLIT: split into multiple cells

Example: Cell A1 contains:
Apple,Orange,Banana
You want each fruit in its own column. Formula in B1:

excel

=TEXTSPLIT(A1, ",")

• A1 is the text to split.
• "," is the column delimiter.

This spills Apple, Orange, Banana into B1:D1 automatically.

You can also split into rows instead of columns:

excel

=TEXTSPLIT(A1, , ";")

Here, the column delimiter is blank and the row delimiter is ";", so each item appears in a new row.

TEXTSPLIT can even use multiple delimiters :

excel

=TEXTSPLIT(A4, {",",";"})

This splits on both comma and semicolon at once.

Formula approach: LEFT, RIGHT, MID, FIND, SEARCH

If you don’t have TEXTSPLIT, you can still split with classic formulas.

Split “first word” and “last word”

Assume A2 = John Smith.

• First word (before first space) in B2:

  excel

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

• Last word (after first space) in C2:

  excel

  =RIGHT(A2, LEN(A2) - FIND(" ", A2))

These patterns (FIND the delimiter, then use LEFT/RIGHT/MID) are the core idea for formula‑based splitting.

Extract middle part

For something like John Michael Smith in A2, you can use MID with two spaces:

excel

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

This pulls the text between the first and second spaces (the middle name).

Advanced: second or third delimiter

If you need to split at a second space (say, after 10 characters), you can add the starting position to FIND, for example:

excel

=LEFT(A1, FIND(" ", A1, 10) - 1)
=RIGHT(A1, LEN(A1) - FIND(" ", A1, 10))

Here, the third argument to FIND tells Excel to start looking after a certain character position.

Flash Fill: “copy the pattern”

Flash Fill is like auto‑complete for patterns and is great when you can show Excel examples of how you want the split to look.

Example story:
You have “Doe, John” in column A and want “John” in B and “Doe” in C.

1. In B2, manually type John.
2. Press Enter , then start typing the next expected result in B3 (e.g., Jane if A3 is “Smith, Jane”).
3. Excel usually suggests the rest of the column; press Enter to accept.
4. Repeat for column C with last names.

This is especially handy when the pattern is obvious but hard to express with a single formula.

“Splitting” into rows (not just columns)

Excel doesn’t literally split a single cell into two smaller ones (no “merge in reverse”), but you can simulate splitting into rows:

1. Use Text to Columns or formulas to split into multiple columns.
2. Copy the resulting cells.
3. Use Paste Special → Transpose to flip columns into rows.

This trick is useful when you want one item per row for further analysis or pivot tables.

Tiny forum‑style Q&A view

Q: Can I divide one cell into two separate physical boxes?
A: Not exactly. Excel cells are a fixed grid; you can split the content into other cells using Text to Columns, formulas, or TEXTSPLIT, but the original cell itself cannot be subdivided.

Q: What if I have multiple spaces or messy delimiters?
A: Enable “Treat consecutive delimiters as one” in Text to Columns, or combine TEXTSPLIT with TRIM to clean up extra spaces.

Q: Which method should I use today?
A: For quick one‑off jobs, Text to Columns. For reusable, dynamic spreadsheets, formulas or TEXTSPLIT. For irregular but visible patterns, Flash Fill.

SEO‑friendly quick notes

• Focus phrase: how to split cells in excel is naturally covered in sections on Text to Columns, TEXTSPLIT, and formulas.

• This remains a trending “how‑to” topic as Excel keeps adding new text functions (like TEXTSPLIT in recent years), so newer answers often mention both classic and modern methods.

Meta description idea:
Learn how to split cells in Excel using Text to Columns, formulas (LEFT, RIGHT, MID), and the newer TEXTSPLIT function, plus tips for splitting into rows and handling messy data.

Information gathered from public forums or data available on the internet and portrayed here.