Text standardization
How to Standardize Text Data in Excel
Standardizing text data means writing the same thing the same way: United States, USA, U.S. and usa become the one label you choose. This guide covers text and category labels. It is not about Excel's STANDARDIZE function, which returns a normalized value from a mean and standard deviation for statistical work. Keep the original column, make every mapping visible and leave unclear labels for a person to decide.
By the TidyX team · Updated
Clean up spaces and case in helper columns
Make a backup copy of the original data first, as Microsoft's own cleanup guide recommends, then build each fix in a new column beside the original so every change can be compared before it replaces anything.
- For labels in column A, enter =TRIM(CLEAN(SUBSTITUTE(A2,UNICHAR(160)," "))) in B2 and fill down. SUBSTITUTE turns nonbreaking spaces into ordinary spaces, CLEAN removes nonprinting characters with codes 0 to 31, and TRIM removes leading and trailing spaces and reduces repeated spaces between words to one.
- In C2, enter =UPPER(B2), =LOWER(B2) or =PROPER(B2) and fill down so every label uses the same case. Do this only where case carries no meaning; leave identifiers whose case matters as they are.
- In D2, enter =EXACT(A2,C2) and fill down. Filter column D for FALSE to see every label the cleanup changed, including case-only changes, before you rely on column C.
Why the formula replaces nonbreaking spaces first
TRIM was designed for the ordinary space character, code 32, and does not remove the nonbreaking space, code 160, which is common in web pages. UNICHAR(160) returns that Unicode character in Excel for Windows and Mac. CHAR(160) instead takes the character from your computer's own character set, and on a Mac that set is different.
Map label variants with a lookup table
Cleaning spaces and case removes formatting noise. Variants such as USA, U.S. and United States need a decision about which label is right, and a two-column mapping table keeps that decision where anyone can check it.
- List each distinct cleaned label once. In Microsoft 365 and Excel 2021 or later, =UNIQUE(C2:C500) returns the list; adjust the range to your data. In older versions, use Data › Advanced with Copy to another location and Unique records only.
- Build the mapping table in H2:I20: each variant in column H, written in the same cleaned form as column C, and the label you want in column I. Give each variant exactly one approved output.
- In E2, enter =XLOOKUP(C2,$H$2:$H$20,$I$2:$I$20,C2) and fill down. A variant listed in the table returns its approved label; any other label comes back unchanged.
- Excel 2016 and 2019 do not have XLOOKUP. Use =IFNA(VLOOKUP(C2,$H$2:$I$20,2,FALSE),C2) instead and keep FALSE, because without it VLOOKUP uses approximate match.
- Review every label that stayed unmapped and every mapping that joins labels with different meanings. Then copy column E and paste it as values into a new column or file, keeping the original.
When case or look-alike labels matter
If case distinguishes values, such as product codes where ab-1 and AB-1 are different items, keep that column out of the case step and the lookup, and check matches with EXACT, which is case-sensitive; Excel's MATCH, for example, does not distinguish between uppercase and lowercase letters. Similar text is not proof of the same meaning either: Paid and Unpaid, or two region codes one letter apart, need a decision from someone who knows the data.
Fix a few variants with Find and Replace
When only a handful of spellings are wrong, Find and Replace (Ctrl+H) can be quicker than a lookup. Check Match entire cell contents so that replacing US does not also change USA or Business, and check Match case only when case should decide the match.
Replace All changes every match at once, directly in the cells, so run it on your copy and add each replacement to the mapping table to keep a record of the decision.
Stop new variants with a drop-down list
Standardizing a column once does not stop the next person from typing Calif. instead of California. For columns that people fill in by hand, put the approved labels in a list and apply Data › Data Validation with Allow set to List, so each cell offers only those choices. On the Error Alert tab, the Stop style rejects entries typed into those cells that are not in the list.
If the approved labels sit in an Excel table, the drop-downs update as you add or remove labels.
Keep ambiguous categories visible
Not every similar label is equivalent. A system should not assume that two product codes, regions, diagnoses, or account statuses belong together without domain context. In TidyX, a proposed mapping stays a suggestion until you review it.
The planning model works from the file's profile plus the context, domain notes and column descriptions you save. It does not receive the file’s full list of labels, and with masked samples off it receives no sample values, so labels reach it only when you write them into your context, domain notes or column descriptions. If you want it to propose a mapping, write the known variants into your instructions or domain notes, then check every pair it returns.
Standardize labels in TidyX with rules you review
The TidyX profile flags columns where it finds leading or trailing spaces in the first 2,000 filled values, and columns with 2 to 50 distinct labels where some labels differ only in case or surrounding spaces. A plan can answer those flags with a trim rule, which removes leading and trailing whitespace but leaves the spaces between words as they are, and a case rule that sets named columns to lower, upper or title case.
For true variants, Replace values and Map categories rules list every from-to pair explicitly, with exact or ignore-case matching. Map categories can also keep the labels you did not map or turn them into missing values. Both are high-risk rules: each needs your acknowledgement before the plan can be confirmed, and the run report records how many cells each rule changed.
A label is mapped only when the whole cell matches an entry, exactly or ignoring case as you choose; similar spellings are never merged on their own. Free-form code and remote lookup URLs are rejected by the rule schema.
- Trim leading and trailing whitespace
- Normalize lower, upper, or title case
- Replace only explicitly mapped values
- Review target columns and impact before running
Handle new categories as schema drift, not a silent fix
Next month's export may bring a spelling that no mapping entry covers. Unmapped labels stay as they are unless the rule was set to turn them into missing values; then the new spelling becomes an empty cell. Check unfamiliar labels before you reuse a mapping.
The compatibility check for reused rules compares columns and their types, not the labels inside them. A saved recipe can create a draft for a compatible profiled project; review that draft, acknowledge enabled high-risk rules and accept a new execution quote. The report then records actual transformations and provenance for that run.