Data Cleanup and Formatting with AI

Messy data is the biggest time-waster in spreadsheets. Names in different formats, inconsistent dates, extra spaces, duplicates—AI can help you fix all of it with the right formulas.

Common Data Problems

Here are the messes AI can fix:

Problem	Example
Inconsistent names	"john smith", "JOHN SMITH", "John Smith"
Mixed date formats	"01/15/2024", "Jan 15, 2024", "2024-01-15"
Extra whitespace	" Hello World "
Combined data in one cell	"John Smith, john@email.com, 555-1234"
Duplicate entries	Same customer listed 3 times

Describing the Mess

The key to good cleanup prompts is showing AI exactly what the messy data looks like and what you want it to become.

AI will generate something like: =PROPER(TRIM(A2))

Splitting Data

When one column contains multiple pieces of information, AI can help split them:

"Column A has full addresses like '123 Main St, Springfield, IL 62701'. I want to split this into Street (column B), City (column C), State (column D), and ZIP (column E)."

AI will use a combination of SPLIT, INDEX, TRIM, and REGEXEXTRACT to break the data apart.

Standardizing Dates

Date formats are a common headache:

"Column A has dates in mixed formats: some are '01/15/2024', some are 'January 15, 2024', and some are '2024-01-15'. I want them all as 'YYYY-MM-DD' in column B."

AI will often suggest DATEVALUE combined with TEXT formatting, or a combination of REGEXMATCH and conditional formatting.

Removing Duplicates

For finding and handling duplicates:

"Column A has email addresses. Some appear multiple times. I want a formula in column B that marks the first occurrence as 'Unique' and any repeats as 'Duplicate'."

AI will give you: =IF(COUNTIF(A$1:A2, A2)=1, "Unique", "Duplicate")

Batch Cleanup with ARRAYFORMULA

Combine cleanup with ARRAYFORMULA for one-shot fixes across your whole column:

"Give me an ARRAYFORMULA that cleans all the names in column A and puts the results in column B."

Result: =ARRAYFORMULA(IF(A2:A="", "", PROPER(TRIM(A2:A))))

Key Takeaway

When asking AI to clean data, always show examples of the messy data and describe the desired clean format. AI is excellent at combining Google Sheets text functions like TRIM, PROPER, SUBSTITUTE, SPLIT, and REGEXEXTRACT into a single formula that fixes your data.