Ravindra BagaleCourses & study guides Track your progress

Excel TEXTSPLIT Function

By · Last updated:

Chala mitrano — tEXTSPLIT cracks a sentence into cells. Comma to columns, line-break to rows — formula style.

Fictional sample data for learning. Address lines and CSV cells (Belagavi warehouses + Lisbon shops). Mixed ₹ and €.

Purpose

Split text into columns or rows using a delimiter — modern Excel’s answer to Text to Columns, as a formula.

Return value

A spilled array of pieces (dynamic array).

Syntax

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Arguments

ArgumentRequired?Description
textRequiredThe string to split.
col_delimiterRequiredDelimiter that creates new columns.
row_delimiterOptionalDelimiter that creates new rows.
ignore_emptyOptionalTRUE to skip empty pieces.
match_modeOptionalCase sensitivity for delimiters.
pad_withOptionalFill value when rows have unequal length.

Version

Microsoft 365 and Excel 2021. Not in older Excel (use Text to Columns or manual formulas). LibreOffice: limited/missing.

How to use — Why → What → How

Why: Belagavi warehouse sheets store 'City, State, Pin' in one cell. Lisbon shops paste 'Product; Qty; €Price'. TEXTSPLIT breaks them without the old wizard every time. Clear the spill range before demos. #SPILL! recovery is half the skill. Mention Text to Columns for older Excel without apology.

What: TEXTSPLIT spills pieces across columns (and optionally rows). It needs room to spill. Older Excel: Data → Text to Columns. Read every argument in the table once before you type; each one changes the answer on purpose.

How — numbered steps:

  1. Select a cell with empty neighbours.
  2. Type =TEXTSPLIT(A2,", ").
  3. Press Enter and watch the spill.
  4. Use ignore_empty TRUE for messy doubles.
  5. On older Excel, use Text to Columns.

Ravindra Bagale's Tip

Friends, #SPILL! means something is blocking the cells where the pieces want to land — clear the range.

Ravindra Bagale's Tip

Friends, sleep on TEXTSPLIT and rebuild example 1 tomorrow without looking. That five-minute test beats rereading the whole page.

Ravindra Bagale's Tip

Friends, after you finish TEXTSPLIT, open yesterday’s real workbook and find one place this function would have saved ten minutes. That habit turns lessons into salary skills.

Download practice file: practice-textsplit.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) City state pin

Belagavi.

Line
Belagavi, KA, 590001
Hubli, KA, 580020
Formula: =TEXTSPLIT(A2,", ")

Result: Belagavi | KA | 590001

What it means: Three spilled columns.

2) Semicolon list

Lisbon.

Line
Bread; 2; 1.20
Formula: =TEXTSPLIT(A2,"; ")

Result: Bread | 2 | 1.20

What it means: Product, qty, € price pieces.

3) ignore empty

Double commas.

Line
A,,C
Formula: =TEXTSPLIT(A2,",",,TRUE)

Result: A | C

What it means: Empty piece skipped.

4) First piece only

Take city.

Line
Belagavi, KA, 590001
Formula: =INDEX(TEXTSPLIT(A2,", "),1)

Result: Belagavi

What it means: First spill element.

5) Wizard fallback note

Excel 2016.

Line
A, B, C
Formula: (Text to Columns wizard)

Result: A | B | C

What it means: Same outcome without TEXTSPLIT.

6) ₹ order line

Qty and amount.

Line
Atta; 5; 420
Formula: =TEXTSPLIT(A2,"; ")

Result: Atta | 5 | 420

What it means: Ready for VALUE on qty/amount.

Common errors and fixes

#SPILL! — Blocked cells. Fix: Clear the spill range.
#NAME? — Old Excel. Fix: Text to Columns.
#N/A pad issues — Uneven rows. Fix: Set pad_with.

Notes and tips

  • Needs dynamic array Excel.
  • Clear space to the right/below.
  • Combine with VALUE for numeric pieces.
  • Warehouse one-liners in Belagavi and shop pastes in Lisbon are everyday TEXTSPLIT fuel. Always teach #SPILL! recovery.
  • TEXTSPLIT vs Text to Columns: formula is live; wizard is static. vs TEXTBEFORE: TEXTBEFORE returns one piece.
  • Fictional sample data only — practice amounts and names are made for learning.
  • After the 10 tasks, re-read the errors table once.
  • Say the business question in plain words before you type equals.
  • Keep a scrap sheet for experiments so you do not overwrite class files.
  • Fill formulas down only after the first row looks correct.
  • When two answers look possible, write both on paper then test in Excel.
  • Clear the spill range before demos. #SPILL! recovery is half the skill. Mention Text to Columns for older Excel without apology.
  • Revisit the TEXTSPLIT errors table after the quiz — most classroom mistakes are listed there.
  • Build one brand-new example on a blank sheet from memory before you close the laptop.
  • If a result looks wrong, check spaces with LEN and case with exact equals before changing the formula.
  • Share one tip from this page with a classmate in Marathi or Hindi — teaching locks the idea.
  • Keep currency symbols consistent inside a single report column even when practice mixes ₹ $ £ € across examples.
  • When Excel versions differ at home and in class, write the fallback formula under the modern one in a comment cell.
  • Print the revision card for TEXTSPLIT and stick it near your monitor for one week of real worksheets.
  • When helping a junior, ask them to predict the result before pressing Enter — prediction builds judgement.
  • Keep a personal notebook of the one mistake you make most often with this function; review it monthly.

In real work

Warehouse one-liners in Belagavi and shop pastes in Lisbon are everyday TEXTSPLIT fuel. Always teach #SPILL! recovery. Clear the spill range before demos. #SPILL! recovery is half the skill. Mention Text to Columns for older Excel without apology. Classroom drill: write three brand-new TEXTSPLIT formulas on a blank sheet using names from your own city — if you can invent them without the page open, you own the skill. Then break one formula on purpose (wrong case, wrong start, extra space) and watch the error; recovering from that break is half of interview readiness.

How it compares

TEXTSPLIT vs Text to Columns: formula is live; wizard is static. vs TEXTBEFORE: TEXTBEFORE returns one piece.

Interview corner

#SPILL! meaning?
Output blocked.
Old Excel?
Text to Columns.
First piece only?
INDEX around TEXTSPLIT.

TEXTBEFORE, TEXTAFTER, TEXTJOIN, FILTER

Practice tasks (10)

  1. Task 1. Split Belagavi, KA, 590001.
    Hint: comma-space.
  2. Task 2. Lisbon Bread; 2; 1.20.
    Hint: semicolon-space.
  3. Task 3. ignore_empty on A,,C.
    Hint: TRUE.
  4. Task 4. INDEX first piece city.
    Hint: INDEX 1.
  5. Task 5. Clear a #SPILL! on purpose then fix.
    Hint: block then clear.
  6. Task 6. Note wizard fallback.
    Hint: old Excel.
  7. Task 7. VALUE on a numeric piece.
    Hint: after split.
  8. Task 8. Fill two address lines.
    Hint: each row formula.
  9. Task 9. ₹ order line split.
    Hint: example 6.
  10. Task 10. Interview: #SPILL!?
    Hint: blocked.

Quick quiz

Q1. TEXTSPLIT needs roughly:
Q2. #SPILL! means:
Q3. Old Excel tool:
Q4. Skip empty pieces:
Q5. Returns:

Answers: Q1=365/2021 dynamic arrays, Q2=Blocked output range, Q3=Text to Columns, Q4=ignore_empty TRUE, Q5=Spilled array (check after you try).

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.

Revision card: TEXTSPLIT
TEXTSPLIT revision card