Cleaning Messy Data

Data rarely arrives clean. Reports exported from accounting software, bank portals, ERPs and websites bring hidden spaces, amounts stored as text, dates with dots, blank cells where a name should repeat, and duplicate rows. Formulas on such data give wrong totals or #N/A errors that look like lookup problems but are really cleaning problems.

This lesson is a cleaning toolkit for one-off jobs. You will find invisible characters, convert text to real numbers and dates, split columns, fill blanks, and remove duplicates safely. If you clean the same report every month, record the steps once in Power Query instead. The basic text functions are in Text Functions.

The messy export

An accounts assistant in Lucknow downloads a list of customer receipts. It looks fine on screen, but nothing adds up:

What is wrong, even though it looks normal:

• A2 has two spaces before the name. A4 ends with a non-breaking space, a special space character (code 160) that web pages and some software use.
• A3 is blank because the export prints a party's name only on its first row.
• The amounts are text: they sit on the left of the cell and =SUM(C2:C6) returns 0.
• The dates use dots, so Excel stores them as text, and date functions return #VALUE!.
• Rows 4 and 5 are the same receipt exported twice.

Work in new columns or a copy of the sheet, check the results, and only then replace the original.

Step 1: Find what you cannot see

LEN counts characters, so it exposes hidden ones. "Priya Nair" has 10 characters:

=LEN(A4)

11

The extra character is invisible. CODE(RIGHT(A4)) returns 160, which identifies the non-breaking space. This matters because TRIM removes only the normal space (code 32), and CLEAN removes only non-printing control characters such as line feeds (codes 0 to 31). Neither removes code 160.

The reliable formula first turns non-breaking spaces into normal ones, then removes control characters, then trims:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Fill it down a helper column. LEN on the results now shows 11 for "Aarav Mehta" and 10 for "Priya Nair". EXACT(F4, "Priya Nair") returns TRUE, which proves the text now matches what a lookup expects.

Step 2: Text amounts into numbers

The amounts use Indian comma grouping and are stored as text. NUMBERVALUE converts text to a number using the separators you name: the decimal separator, then the group separator.

=NUMBERVALUE(C2, ".", ",")

C2: 125000
C3: 45000
C4: 18500

Because you tell it the separators, the result does not depend on the computer's regional settings. After converting all five rows, =SUM() of the new column gives 279250.

For a quick fix without formulas, select the column and use Data > Text to Columns > Finish; Excel re-enters each value and turns plain number-like text, such as "45000", into numbers. Text with commas may not convert this way on every computer, which is why NUMBERVALUE is the safer choice. A green triangle with "Number Stored as Text" also offers Convert to Number for selected cells.

Step 3: Dates with dots

"03.10.2026" is day 3, month 10. Build the real date from its parts with DATE(year, month, day):

=DATE(RIGHT(D2, 4), MID(D2, 4, 2), LEFT(D2, 2))

46298

46298 is 3 October 2026; apply a date format to see it. The menu way is Data > Text to Columns: choose Delimited, click Next twice, and in step 3 set Column data format to Date: DMY. Excel then reads every value in day-month-year order, whatever the computer's settings. Use this also for slash dates such as 03/10/2026 that Excel has misread as 10 March.

Step 4: Split one column into several

Exports often pack two facts into one cell, such as "Kanpur - UP". Text to Columns with Delimited splits at a character you choose (comma, space, tab or Other, for example -). Choose a destination column so the original stays intact. In Microsoft 365, =TEXTSPLIT(B2, " - ") does the same with a formula, and for patterns that are hard to describe, type one or two examples and press Ctrl+E for Flash Fill, as shown in Entering & Editing Data.

Step 5: Fill blank cells from above

A3 should say Aarav Mehta, like the row above. On a real export with hundreds of such gaps, fill them all at once:

1. Select the Party column, A2:A6.
2. Press F5 (Go To), click Special, choose Blanks and click OK. Only the empty cells stay selected.
3. Type = and press the Up Arrow, so the formula points to the cell above, for example =A2.
4. Press Ctrl+Enter to enter it in every selected blank cell.
5. Select the column again, copy, and Paste Special > Values, so the names stop being formulas.

Step 5 matters: if you later sort the data, formulas pointing "to the cell above" would point at the wrong rows.

Step 6: Duplicates, safely

Data > Remove Duplicates deletes rows permanently, and you cannot see what it removed. Flag duplicates first with a running count. In a helper column, filled down from row 2:

=COUNTIFS($A$2:A2, A2, $C$2:C2, C2, $D$2:D2, D2)

Row 2: 1
Row 3: 1
Row 4: 1
Row 5: 2
Row 6: 1

The range grows as the formula is copied down, so each row counts how many times its party, amount and date have appeared so far. Any value above 1 is a repeat. Filter the column for values above 1, check them with the person who sent the data (two equal receipts on one day can be genuine), and only then delete them or run Remove Duplicates with the same three columns ticked. To list distinct values without deleting anything, use UNIQUE from Dynamic Arrays.

Common mistakes

• Trusting TRIM alone. Non-breaking spaces survive TRIM. Use SUBSTITUTE(..., CHAR(160), " ") first.
• Deleting the original column too early. Keep raw data until the cleaned version is checked; then paste values.
• Find and Replace on dates. Replacing "." with "/" lets Excel guess the order, and some dates become wrong months. Use Text to Columns with DMY or DATE.
• Removing duplicates on one column. Ticking only Party deletes every second receipt from the same customer. Tick all the columns that define a true repeat.
• Leaving fill-down formulas in place. Paste them as values before sorting.

Key points

• LEN and CODE reveal hidden characters; TRIM and CLEAN do not remove CHAR(160).
• TRIM(CLEAN(SUBSTITUTE(text, CHAR(160), " "))) is a reliable text cleaner.
• NUMBERVALUE(text, ".", ",") turns Indian-format text amounts into numbers.
• Text to Columns splits columns and converts dates with a chosen order such as DMY.
• Go To Special > Blanks, = Up Arrow, Ctrl+Enter fills gaps; flag duplicates with a running COUNTIFS before removing them.

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))
=NUMBERVALUE(B2, ".", ",")
=DATE(RIGHT(C2, 4), MID(C2, 4, 2), LEFT(C2, 2))

12500
46333  (07-Nov-2026 after date format)