26. Excel Tricks Every Analyst Should Know

26.2 Data Cleaning Tricks

By Ravindra Bagale · Last updated: 6 October 2026

Example first. An export has extra spaces, amounts stored as text, repeated Order IDs and City and PIN in one cell. Four quick fixes make it usable.

Trick 5. Remove extra spaces with TRIM is shown in the box below; tricks 6–8 follow it.

First look at the data (before → after):

Expected result — After =TRIM(A2)

B2: =TRIM(A2)
C2: =LEN(B2)

Trick 6. Turn numbers stored as text into numbers

Before:

After:

B2: =VALUE(A2)
B5: =SUM(B2:B4)  → 665

Steps:

1. In B2 type =VALUE(A2) and fill down; or select A2:A4, click the green-triangle warning and choose Convert to Number.
2. Total with =SUM(B2:B4).
3. Paste the values back over column A if you need them there.

Trick 7. Remove duplicate rows

Before:

After:

Steps:

1. Click inside the list.
2. Click Data › Data Tools › Remove Duplicates.
3. Tick the column(s) that define a duplicate and click OK.
4. Excel reports: 2 duplicate values removed; 4 unique values remain.
5. Work on a copy: Remove Duplicates deletes rows.

Trick 8. Split one column into two with Text to Columns

Before:

After:

Steps:

1. Select A1:A3 (make sure column B is empty).
2. Click Data › Data Tools › Text to Columns › Delimited › Next.
3. Tick Other and type -, click Next.
4. Set column B to Text so the PIN keeps every digit, and click Finish.

Practice task (try it before you open the answer):

1. Convert "450", "120", "95" (text) to numbers and total them.
2. Remove duplicates from the 6 Order IDs.
3. Split "Nashik-422001" into City and PIN.