Excel Intermediate Lesson 5: Cleaning Text With TEXTSPLIT, TEXTBEFORE and TEXTAFTER

📎 This article includes 1 downloadable practice file ↓

📘 Excel Intermediate Course · Lesson 5 of 12

Exports from Tally, SAP, bank portals and old software rarely arrive clean. A typical line looks like this:

INV-1001 | Neha Sharma | DELHI

Spaces at both ends, a separator, shouting capitals. Here is how to take it apart in one formula each.

The new text functions (Microsoft 365)

A negative instance number counts from the end, so TEXTAFTER(A2,"|",-1) is “after the last pipe” no matter how many there are.

The classic helpers still matter

• TRIM removes spaces at both ends and squeezes double spaces inside.
• PROPER, UPPER, LOWER fix case.
• SUBSTITUTE(A2,"INV-","") removes a prefix; wrap in VALUE to get a number.
• CLEAN removes line breaks and invisible characters from PDF copy-paste.

Older Excel?

Without TEXTBEFORE: =TRIM(LEFT(A2,FIND("|",A2)-1)). Or use Data > Text to Columns, or Power Query (covered in the Power Query course), which is the better tool for repeating monthly imports.

Common mistakes

• Cleaning in place. Keep the raw column; clean in a new column so you can always trace back.
• Forgetting the result is text. “1005” from a formula won’t add up until you wrap it in VALUE.

Practice

Download this lesson’s workbook below. The Raw sheet holds five lines from a pipe-separated export. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.