Standardize Inconsistent Data in Excel: One Spelling Each

To standardize data in Excel means making every value that refers to the same thing look exactly the same: "NY", "N.Y.", "new york" and "NYC" all become "New York". Until you do, COUNTIF, SUMIF and pivot tables count them as four different cities. This guide shows a repeatable method: find the variants, clean the invisible differences, then map each variant to one spelling with XLOOKUP. It's for anyone who cleans exports, survey answers or customer lists.

Why inconsistent data breaks your reports#

Excel compares text character by character, so any small difference makes a new value. A trailing space, a full stop or an abbreviation is enough. Take this column of cities:

(␣ marks a space you can't see in the cell.) Four of the five rows mean New York, but =COUNTIF(A2:A6,"New York") returns 1. Only "new york" matches, because COUNTIF ignores capitals but not spaces, dots or abbreviations. A pivot table would show four separate New York rows.

Step 1: List every variant with UNIQUE#

Before you fix anything, see what you're dealing with. In an empty column, type:

=SORT(UNIQUE(A2:A500))

The result spills a sorted list of every distinct value, so variants sit next to each other: "N.Y.", "new york", "NY", "NYC". UNIQUE and SORT need Microsoft 365 or Excel 2021. In older Excel, click the column's filter arrow (Ctrl+Shift+L turns filters on; Mac: ⌘⇧F): the list shows each value once.

UNIQUE ignores capitals, so "new york" and "New York" appear as one item. Spaces still count, so if you see what looks like the same value twice, one of them probably has a hidden space.

Step 2: Remove spaces and invisible characters#

Many variants are the same word with extra characters around it. Remove those first, so the mapping table in Step 4 stays short.

The TRIM function reference covers the details. If TRIM seems to do nothing, the culprit is usually the non-breaking space; the guide to why TRIM isn't working shows how to find it.

Step 3: Fix punctuation and abbreviations with SUBSTITUTE#

SUBSTITUTE replaces one piece of text with another inside a cell. Use it to strip punctuation that creates extra variants:

=SUBSTITUTE(A4,".","")

This turns "N.Y." into NY. Replacing with "" (empty text) simply deletes the character. You can nest SUBSTITUTE to remove several characters at once: =SUBSTITUTE(SUBSTITUTE(A2,".",""),",","") removes dots and commas.

Be careful with whole-word abbreviations. Replacing "St" with "Street" also changes "Stockton" to "Streetockton". For word swaps, use the mapping table in the next step instead. The SUBSTITUTE function reference and the comparison of SUBSTITUTE and REPLACE explain the optional fourth argument, which replaces only one occurrence.

Step 4: Map every variant to one spelling with XLOOKUP#

A mapping table is a small two-column list: each variant on the left, the correct spelling on the right. XLOOKUP then swaps every raw value for its standard version.

In B2, next to the raw data, type:

=XLOOKUP(TRIM(A2),$C$2:$C$6,$D$2:$D$6,TRIM(A2))

Then fill down to B6. Here's what each part does:

• TRIM(A2) cleans the value before the lookup, so "␣␣NY" becomes "NY".
• $C$2:$C$6 is where to look, and $D$2:$D$6 is what to return. The $ signs keep them fixed as you fill down.
• The last TRIM(A2) is what to return if the value isn't in the table, so new values pass through unchanged instead of showing #N/A.

The result in B2 is New York, and all four New York rows now match. =COUNTIF(B2:B6,"New York") returns 4.

XLOOKUP isn't case-sensitive, so "new york" matches the "New York" row without its own entry. XLOOKUP needs Microsoft 365 or Excel 2021; in older Excel use =IFERROR(VLOOKUP(TRIM(A2),$C$2:$D$6,2,FALSE),TRIM(A2)). The XLOOKUP function reference lists every argument.

To spot values that aren't in the table yet, use "CHECK" as the not-found value instead, then filter column B for CHECK and add the new variants to the mapping table.

When the column looks right, copy it and paste as values (Windows: Ctrl+Alt+V, then V; Mac: ⌃⌘V) over the raw data, so the clean version no longer depends on the formula.

Step 5: Make capitals consistent#

If the only difference is capitals, you don't need a mapping table. PROPER, UPPER and LOWER set the case in one step:

PROPER can get names like "McDonald" or "USA" wrong, so check the results. The guide to changing case in Excel covers the exceptions.

Quick fixes with Find and Replace#

For one or two variants, Find and Replace is faster than formulas. Press Ctrl+H (Mac: Control+H), type the variant and the standard spelling, click Options and tick Match entire cell contents. Without that tick, replacing "NY" would also change "SONY" into "SONew York".

Select the column first so the change stays inside it. The guide to Find and Replace tips covers wildcards and the Replace All mistakes to avoid. For a full clean-up checklist, see how to clean messy data in Excel.

Stop new variants with a drop-down list#

Cleaning fixes today's data; a drop-down list protects tomorrow's. Put your standard values in a list, select the entry column and go to Data > Data Validation. Choose Allow: List and point Source at the standard values. Now people pick "New York" from a menu instead of typing their own version.

Practise it#

Standardizing data is a mix of cleaning functions and a lookup, and both get faster with practice. Practise cleaning messy text in a live spreadsheet with TRIM, SUBSTITUTE and PROPER, then practise XLOOKUP to map values to a standard list. Each formula is checked instantly.