Cleaning messy sales data in Excel

Every analyst inherits the same file: a GST sales register exported from a billing software, where customer names have trailing spaces, dates are text, amounts carry a rupee symbol and half the invoices appear twice. Formulas and pivot tables are useless until this is fixed. Cleaning is not glamorous work, but it is where most Excel time goes.

Spot the problem first

Before fixing anything, diagnose. Three quick checks tell you almost everything:

=SUMPRODUCT(--(A2:A500<>TRIM(A2:A500)))   ' how many cells have stray spaces
=COUNT(C2:C500)                            ' counts only real numbers/dates
=SUMPRODUCT(--(COUNTIF(A2:A500,A2:A500)>1))' how many values repeat

If COUNT on your Taxable Value column returns 0 while the cells look like numbers, they are text. A fast visual clue: numbers align right by default, text aligns left.

Text functions that do the heavy lifting

Problem	Formula	Result
" sharma traders "	=PROPER(TRIM(A2))	Sharma Traders
"₹1,24,500" stored as text	=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"₹",""),",",""))	124500
Non-printing characters from a PDF paste	=TRIM(CLEAN(A2))	clean text
GSTIN in mixed case	=UPPER(TRIM(C2))	08AABCS1429B1ZR
Need the state code from a GSTIN	=LEFT(C2,2)	08 (Rajasthan)
Need the PAN inside a GSTIN	=MID(C2,3,10)	AABCS1429B
Date to a readable label	=TEXT(D2,"dd-mmm-yyyy")	07-Apr-2026

TRIM removes leading, trailing and repeated inner spaces but keeps single spaces between words. CLEAN strips non-printing characters. They are usually used together.

In Microsoft 365 you also get TEXTBEFORE, TEXTAFTER and TEXTSPLIT, which are far easier to read than nested LEFT/FIND combinations:

=TEXTBEFORE(A2," - ")              ' "Gupta Textiles - Surat" -> Gupta Textiles
=TEXTAFTER(A2," - ")               ' -> Surat
=TEXTSPLIT(A2," - ")               ' spills both parts into two cells

Text to Columns: the date fixer

Text to Columns is famous for splitting Name, City into two columns, but its best use is repairing text-dates. Select the column, go to Data → Text to Columns → Next → Next, choose Date: DMY and click Finish. Every 07/04/2026 string becomes a real date serial number, ready for grouping in a pivot table.

The same wizard with Delimited → Other: / splits INV/2026/0417 into three parts when you need the invoice series separately.

Flash Fill: pattern matching without formulas

Type the answer you want in the first cell next to your data, then press Ctrl+E. Excel reads the pattern and fills the rest. It is excellent for extracting a city from an address line, building Firstname L. labels, or masking a mobile number as 98XXXXXX21.

Flash Fill produces static values, not formulas. If the source data changes tomorrow, the filled column does not update — so use it for one-off cleanup, and formulas for recurring reports.

Remove Duplicates

Billing exports often repeat rows when an invoice is amended. Select the range, then Data → Remove Duplicates, and tick only the columns that define uniqueness — usually Invoice Number alone, not every column, because an amended row differs in the amount and would survive the check.

Always keep a copy of the raw sheet before removing anything. Duplicates removal cannot be undone once the file is saved and closed.

Common mistakes

• Cleaning in place, so the original export is lost and the work cannot be re-checked.
• Using =A2*1 to convert text numbers and getting #VALUE! because the cell still has a rupee symbol or a space.
• Forgetting that TRIM does not remove the non-breaking space (CHAR(160)) from web pastes; add =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
• Running Remove Duplicates on a filtered view and assuming hidden rows were checked.