Tips & Tricks - 2026-05-12

How to Split Text in Excel

Split text in Excel using Text to Columns, TEXTSPLIT, LEFT, RIGHT, MID, TEXTBEFORE, and TEXTAFTER with practical examples.

Ways to Split Text in Excel

  • Splitting text is one of the most common data cleanup tasks in Excel. You might need to split names, emails, product codes, addresses, or comma-separated values. The best method depends on your Excel version and whether the split should update automatically.

Method 1: Text to Columns

  • Text to Columns is the classic manual tool. Select the cells. Go to Data -> Text to Columns. Choose Delimited. Pick the delimiter: comma, space, tab, or custom. Choose the destination. Use this when you need a quick one-time split.

Method 2: TEXTSPLIT

  • TEXTSPLIT is best for dynamic splitting in Microsoft 365. \\\ =TEXTSPLIT(A2,",") \\\ This splits text by commas. For spaces: \\\ =TEXTSPLIT(A2," ") \\\

Method 3: TEXTBEFORE and TEXTAFTER

  • Use TEXTBEFORE to get text before a delimiter: \\\ =TEXTBEFORE(A2,"@") \\\ Use TEXTAFTER to get text after a delimiter: \\\ =TEXTAFTER(A2,"@") \\\ This is useful for emails, IDs, and codes.

Method 4: LEFT, RIGHT, and MID

  • Use these when the text has fixed positions. \\\ =LEFT(A2,3) =RIGHT(A2,4) =MID(A2,5,2) \\\ These are useful for product codes and structured IDs.

Common Split Text Problems

  • Extra spaces after splitting Inconsistent delimiters Empty values between repeated delimiters Fixed-width text that changes length Use TRIM and CLEAN after splitting imported data.

Related Guides

  • TEXTSPLIT function TEXTBEFORE function TEXTAFTER function LEFT function TRIM function

Split Text Method Matrix

  • Situation Best method One-time split Text to Columns Dynamic split in Microsoft 365 TEXTSPLIT Get text before a delimiter TEXTBEFORE Get text after a delimiter TEXTAFTER Fixed positions LEFT, RIGHT, MID Repeatable imported cleanup Power Query

Expert Cleanup Tip

  • Always inspect inconsistent delimiters before splitting. Imported data may contain commas, semicolons, tabs, extra spaces, or line breaks. Clean first when needed: \\\ =TRIM(CLEAN(A2)) \\\ Then split the cleaned result.

Related Guides

  • Use Text to Columns for one-time splits, TEXTSPLIT for dynamic arrays, and Power Query for repeatable cleanup.

Frequently asked questions

  • How do I split text by comma in Excel? Use Text to Columns for a one-time split or =TEXTSPLIT(A2,",") for a dynamic formula-based split.
  • What is the difference between Text to Columns and TEXTSPLIT? Text to Columns changes the data once. TEXTSPLIT is a formula that updates automatically when the source text changes.
  • What is the best way to split names in Excel? Use Text to Columns for a one-time split, or TEXTBEFORE and TEXTAFTER when you need formulas that update automatically.
  • Why did my split text create extra blank columns? The source may contain repeated delimiters, extra spaces, or inconsistent separators.