Normalizing Data in Excel: Spaces, Case, Dates and Phone Numbers

Normalizing data means making sure the same information is always written the same way. It sounds like a minor detail, but it is what makes filters work, lookups find what you are looking for, PivotTables group correctly and duplicates get detected. If your customer list contains “Boston”, “boston” and “BOSTON “, your reports will show three different cities. In this article you will see the most useful techniques to normalize the most common data types.

Why normalize

• Correct grouping: a PivotTable only groups identical values.
• Reliable lookups: VLOOKUP or XLOOKUP fail if there is an extra space.
• Duplicate detection: two identical records written differently are not recognized as duplicates.
• Error-free imports: target systems expect specific formats.
• Clean comparisons: when you compare two versions, formatting differences are not mistaken for real changes.

Spaces

This is the most frequent and the most invisible problem.

• =TRIM(A2) removes leading and trailing spaces and leaves a single space between words.
• The non-breaking space (common when copying from websites) is not removed by TRIM. Combine them like this: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
• To remove every space, for example in a code or phone number: =SUBSTITUTE(A2," ","").

Upper and lower case

• =PROPER(A2) capitalizes the first letter of each word. Ideal for names and cities, although check cases like “of” or “the”, which will also be capitalized.
• =UPPER(A2) for codes, license plates or identifiers.
• =LOWER(A2) for email addresses and web addresses.

Dates

Dates are the data type with the most variants: “09/30/2026”, “2026-09-30”, “30-Sep-26”, “30.09.2026” or even “20260930”.

Dates stored as text

If dates are left-aligned, they are probably text. Try =DATEVALUE(A2) and apply a date format to the result. If that fails because of the day/month order, use Data > Text to Columns, go to step 3, choose Date and the correct order (DMY, MDY or YMD).

Dates in YYYYMMDD format

For a value like 20260930 use: =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)).

Choosing a final format

To work in Excel, any display format is fine as long as the value is a real date. To export to other systems, the ISO format YYYY-MM-DD is the safest: =TEXT(A2,"yyyy-mm-dd").

Phone numbers

Decide on a standard format, for example with the international prefix and no spaces (+15551234567), and apply it:

1. Remove spaces, hyphens, dots and parentheses with several nested SUBSTITUTE functions.
2. Add the prefix if it is missing: =IF(LEFT(B2,1)="+",B2,"+1"&B2).
3. Store the column as text so the “+” sign and zeros are not lost.

Codes and references

• If codes must have a fixed length with leading zeros: =TEXT(A2,"000000").
• If they mix hyphens and spaces, standardize them with SUBSTITUTE.
• Make sure the column is formatted as text before pasting the values.

Categories and repeated values

For columns with values from a list (countries, departments, order statuses), the best method is:

1. Copy the column to a separate sheet and use Remove Duplicates to see every variant.
2. Build a mapping table with two columns: original value and normalized value.
3. Use XLOOKUP or VLOOKUP to replace each value with its normalized version.

Then apply Data Validation to that column so only values from the list can be entered in the future.

Amounts and numbers

• Remove currency symbols and thousands separators with SUBSTITUTE.
• If the decimal separator is wrong (comma instead of period or vice versa), use NUMBERVALUE, which lets you specify the separators: =NUMBERVALUE(A2,",",".").
• Round to two decimals with ROUND if amounts come with floating-point decimals.

Paste as values

All these formulas go in helper columns. When the result is correct, copy the helper column, paste it as values over the original and delete the helper. That way the file stays clean with no unnecessary formulas.

Common normalization mistakes

• Overwriting the original column before checking the result. Always work in a helper column and paste as values only when you are sure.
• Applying PROPER to everything. Acronyms like “LLC”, “VAT” or “USA” will end up wrongly capitalized; review them afterwards.
• Turning codes into numbers. When you remove spaces or hyphens from a code, Excel may read it as a number and drop leading zeros. Apply text formatting first.
• Normalizing only part of the data. If you apply a rule to the first thousand rows and forget the rest, the column will be mixed and filters will still fail.
• Not documenting the rules. Write down on a separate sheet which rule you applied to each column. Next time you receive data you can repeat it exactly, and other people on the team will know how it was cleaned.

Frequently asked questions

Conclusion

Normalizing data is the foundation of any reliable analysis. Trim spaces, standardize case, convert text dates, standardize phone numbers and codes, and use mapping tables for categories. Then remove duplicates and verify the result. With normalized data, your reports will be faster to prepare and much easier to trust. Discover more utilities on our Excel tools page.