Press Enter to search  ·  Esc to close

Claude AI — Excel Course
Use Claude to Clean and Standardise Imported Data in Excel
Progress1 of 4
Section 01

Imported data breaking your formulas? Here's the fix.

Every import from a CRM, accounting system, HR platform or supplier CSV brings the same issues: dates that won't sort, numbers stored as text, trailing spaces, inconsistent capitalisation, and duplicates that aren't detected. These problems are often invisible — yet they break your formulas and waste hours.

Claude can't clean the data for you, but it excels at two things: diagnosing exactly what's wrong from a small sample, and giving you the precise formulas to fix it fast.

Import_Data
Cleaned
Summary
⚠ Raw import (broken)
✓ After cleaning
2
"smith, john "  
Smith, John
3
'01/15/2026
15/01/2026
4
"£ 4,250.00"
4250
5
LONDON   
London
6
"1042 " (text)
1042 (number)
Spaces · wrong case · text numbers · apostrophe dates
TRIM + PROPER + VALUE + DATEVALUE applied

Paste a few rows, explain the issue, and Claude returns the right combination of cleaning formulas — plus clear instructions on how to apply them safely.

The four most-used cleaning formulas — and when to use each:

TRIM
=TRIM(A2)
Removes leading, trailing, and double spaces. Use on any text column from an external system.
"London  "→"London"
VALUE
=VALUE(A2)
Converts text that looks like a number into a real number. Fixes SUMIFS returning 0.
"4250" (text)→4250 (number)
PROPER
=PROPER(A2)
Capitalises the first letter of each word. Use on name columns with inconsistent case.
"SMITH, JOHN"→"Smith, John"
DATEVALUE
=DATEVALUE(A2)
Converts a date stored as text into a real Excel date. Fixes dates that sort alphabetically.
"01/15/2026" (text)→real date
  • Paste sample rows and Claude identifies invisible problems — spaces, type mismatches, inconsistent formats
  • Claude writes the cleaning formula for each column — TRIM, VALUE, PROPER, DATEVALUE or a combination
  • Claude tells you whether to fix in place or use a helper column
  • Claude writes a reusable cleaning template for every future import from the same source
Never overwrite original imported data. Always apply cleaning formulas in a helper column first. If the formula is wrong, you still have the original to fall back on.

Six steps — from raw imported data to a clean, formula-ready column.

B2
fx
=TRIM(PROPER(A2))
1
A — Raw import
B — Helper column
C — Final (values)
2
"SMITH, JOHN  "
=TRIM(PROPER(A2))
Smith, John
3
"JONES, SARAH   "
=TRIM(PROPER(A3))
Jones, Sarah
4
"TAYLOR, MIKE"
=TRIM(PROPER(A4))
Taylor, Mike
01

Diagnose the issue quickly

Run a SUMIFS or COUNTIF on the imported data. If it returns 0 when matches exist, you have a formatting problem. Sort a date column — if it sorts alphabetically instead of chronologically, the dates are stored as text.

02

Send a sample to Claude

Copy 5–10 rows from the problematic column. Tell Claude what the column should contain and exactly what's going wrong. The more specific, the faster the diagnosis.

03

Get the fix

Claude identifies the problem and gives you the exact cleaning formula for that column — whether that's TRIM, VALUE, PROPER, DATEVALUE, CLEAN, or a combination like =TRIM(PROPER(A2)).

04

Use helper columns only

Never overwrite the original imported data. Apply the cleaning formula in a new column next to the original. Verify the results on a sample of rows before copying the formula down the full column.

05

Paste as values and replace

Once the helper column looks correct, copy it, then Paste Special → Values only into a new column. Delete the original imported column and the formula column. You now have clean data.

06

Build a reusable template

Ask Claude to create a permanent cleaning checklist for that specific data source. Next time you import from the same system, you have the fixes ready — the whole process takes minutes instead of hours.

Tip: You can combine cleaning functions. =TRIM(PROPER(A2)) removes spaces AND fixes capitalisation in one formula. Ask Claude to combine the functions you need — it handles nested combinations cleanly.

Copy these prompts into Claude. Replace the bracketed placeholders with your actual data and problem description.

Diagnostic prompt

Prompt — paste into Claude
I have imported data with issues. Here are 5 sample rows: [paste rows] This column should contain [dates / numbers / names / IDs]. The problem is [SUMIFS returns 0 / dates won't sort / duplicates not detected / COUNTIF gives wrong count]. Diagnose the issue and give me the exact formula to fix it.

Cleaning formula prompt

Prompt — paste into Claude
I have a column of [employee names / invoice amounts / dates] from [system name]. Common issues: trailing spaces and text formatting. Give me the best cleaning formula to use in a helper column next to the original data.

Reusable template prompt

Prompt — paste into Claude
I import data monthly from [system name] with recurring issues: [list the problems]. Create a complete reusable cleaning checklist and formula set I can apply every time I import this data.
Checklist
Diagnostic test run — SUMIFS/COUNTIF or sort confirmed the problem
Sample rows and problem description pasted into Claude
Cleaning formula applied in helper column only — original data untouched
Results verified on multiple rows before copying formula down
Helper column pasted as values into final column
Original imported column and helper column deleted
Reusable template saved for next import

CLEAN vs TRIM: TRIM removes spaces. CLEAN removes non-printable characters — the hidden characters that appear when you import from legacy systems or mainframes. If TRIM doesn't fix the problem, try =CLEAN(TRIM(A2)).