10 AI Prompts for Cleaning Messy Excel Data

Data from billing software, websites, marketplaces and colleagues is rarely clean. Before you can sum, filter or pivot, it needs fixing. These ten prompts get ChatGPT, Copilot, Gemini or Claude to give you the exact formula or steps for the most common problems.

Two rules before you start:

• Always tell the AI your Excel version (for example, Excel for Microsoft 365 or Excel 2016). Some functions only exist in newer versions.
• Clean in new columns or a copy of the sheet, never in the original data.

1. Extra spaces

Column B has customer names with extra spaces at the start, end and
between words. Give me a formula for C2 to clean them, and explain how to
replace the original column with the cleaned values.

Usually: =TRIM(B2). For spaces copied from websites, the AI may suggest also replacing CHAR(160).

2. Mixed capitals

Names in column B are in mixed case like "SHARMA traders". Convert them
to proper case, but keep short forms like "LLP" and "Pvt Ltd" correct.

3. Dates stored as text

Column A has dates as text like "05-11-2026" meaning 5 November 2026
(DD-MM-YYYY). Give me a formula that converts them to real Excel dates
regardless of my Windows date settings.

A safe answer builds the date from its parts with DATE, RIGHT, MID and LEFT. Check rows where the day is 12 or less.

4. Numbers stored as text

Column E has amounts stored as text, some with commas and "₹". How do I
convert them all to numbers?

5. Splitting a column

Column C has "City - State" like "Nagpur - Maharashtra". How do I split it
into two columns? Give me both a Flash Fill method and a formula method.

Flash Fill (Ctrl+E) is often fastest: type the first result yourself, then press Ctrl+E.

6. Finding duplicates before deleting

I have 3,000 rows of invoices. Column A is Invoice No. Some invoices
appear twice. How do I mark duplicates so I can review them before
deleting anything?

Usually a helper column like =COUNTIF($A:$A, A2)>1 plus a filter.

7. Filling blanks from the row above

My export has the party name only on the first row of each group, and
blank cells below. How do I fill each blank with the value from above?

The classic method uses Go To Special → Blanks, then a formula and Ctrl+Enter.

8. Removing subtotal and title rows

My report export has title rows at the top and subtotal rows inside the
data (they have "Total" in column B). What is the safest way to remove
them and keep only data rows?

9. Standardising labels

Column D has city names written in different ways: "Mumbai", "Bombay",
"mumbai ", "MUMBAI". How do I find all the variations and standardise
them to one name?

Filter the column to see the unique values, or use UNIQUE in Excel 365, then replace.

10. A full clean-up plan for an export

Here are the first 10 rows of a messy export (names replaced): [paste].
I want one clean table with columns: [list]. List every cleaning step in
order for Excel 365, with formulas where needed. Do not delete anything
until I have checked.

After cleaning: always check

• Row count before and after (only changed where you removed rows on purpose)
• Totals of amount columns before and after
• A few random rows compared with the original

Learn more

Data cleaning is lesson 4 of our free course AI for Excel, which also covers lookups, pivot tables, charts and macros.