How to Clean Names in Excel: Fix Spaces, Case, and More

Errors & fixes · Updated November 20, 2026

To clean names in Excel, keep the original list, then use a formula to remove extra spaces and hidden characters and apply consistent capitalization. With a source name in A2, enter the formula below in B2, fill it down, review the results, and copy the cleaned names back as values only when you are satisfied. This approach helps make names easier to read, sort, match, and use in reports without overwriting your source data.

What You’ll Need

  • A workbook in Microsoft Excel for Microsoft 365 on Windows, with names in one column and preferably one name per cell.
  • An empty column beside the original names for the cleaned results.
  • A saved copy of the workbook, especially if the names are important or the list is large.

The examples below assume the source names start in A2 and continue down column A, with a header in A1. If your list starts elsewhere, replace A2 in the formulas with the address of its first name. Excel for Mac has the same functions and nearly identical ribbon commands described here; Excel for the web also supports the formulas, though some ribbon labels or locations may vary slightly.

Step-by-Step

  1. Preserve the original list. Save the workbook with File > Save As, or duplicate the worksheet by right-clicking its sheet tab and selecting Move or Copy. Keeping an untouched source column makes it possible to compare results and undo a cleanup that changes a name incorrectly. Add a clear header such as Cleaned Name to the new column so you do not confuse it with the source.
  2. Check what needs fixing before choosing a format. Scan several rows for leading or trailing spaces, repeated spaces between words, inconsistent capitalization, blank cells, and odd gaps that may be nonbreaking spaces. Also look for titles such as Dr. or Ms., suffixes such as Jr. or III, and names with apostrophes or hyphens. Decide whether capitalization should be standardized or preserved; a formula cannot know that a person styles their name unusually.
  3. Remove extra spaces and hidden control characters. With source names in A2:A100 and the cleaned result starting in B2, enter this formula in B2, then fill it down. The formula replaces nonbreaking spaces with ordinary spaces before removing control characters and trimming excess spaces.

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

For example, a cell containing a leading space, “Ava” followed by several spaces, and “Chen” becomes “Ava Chen.” TRIM removes spaces at the beginning and end and reduces repeated ordinary spaces between words to one. CLEAN removes many nonprinting control characters, while SUBSTITUTE handles a common invisible character that TRIM does not remove: the nonbreaking space, represented here by CHAR(160).

To fill the formula down, select B2 and double-click the small square at the lower-right corner of the cell, called the fill handle. Excel copies the formula alongside adjacent data. You can also drag the handle down to the last row. Check that the range did not stop early, particularly if there are blank cells in the source column.

  1. Standardize capitalization if that suits the list. If you want each word to begin with a capital letter, use PROPER as part of the cleanup formula. With names in A2:A100 and the result beginning in B2, enter this formula in B2 and fill it down:

=PROPER(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))))

A value such as “ JORDAN LEE ” becomes “Jordan Lee.” PROPER changes the case of every word, so review names such as “McDonald,” “van der Meer,” “O’Neil,” and names with intentional internal capitals. The result might not match the person’s preferred spelling. Keep the original column and correct those exceptions manually in the cleaned column.

If you want to fix spacing but retain the existing capitalization, use the formula from step 3 instead. For all-uppercase or all-lowercase output, Excel’s UPPER and LOWER functions are alternatives. With the original value in A2 and the output in B2, the corresponding formulas are =UPPER(A2) and =LOWER(A2). These change capitalization only; they do not remove extra spaces or hidden characters.

  1. Inspect blanks and unusual results. Compare the first, middle, and last rows, as well as names that contain multiple words, punctuation, or accents. A blank result may indicate a blank source cell or a cell containing only spaces and hidden characters. Do not automatically delete blank rows: first confirm that they are not separating sections or marking missing information.
  2. Check for duplicates after cleanup. Cleaning can reveal that two entries differ only because of spaces or capitalization. With cleaned names in B2:B100, enter the following in C2 and fill down to flag repeated names:

=COUNTIF($B$2:$B$100,B2)>1

For example, if both B2 and B7 contain “Ava Chen,” the formula returns TRUE for each matching row. A TRUE result is a review flag, not proof that a record should be removed: two different people can share a name. Check other identifying information before deleting or merging any records.

  1. Turn the formula results into regular text when ready. Select the cleaned cells in column B and press Ctrl+C on Windows. Right-click the selection and choose Paste Special > Values, or use Home > Paste > Values. This replaces formulas with the displayed names, so the results no longer depend on the source column. On Mac, use Command+C to copy; the Paste Special or Home > Paste > Values options are available there too. If you need to replace the original column, first confirm that the values look right, then paste values over it.
  2. Save and use the cleaned column consistently. Save the workbook after checking the results. For sorting or matching, use the cleaned column throughout the task rather than mixing cleaned and uncleaned versions. If another person or system needs the data, clarify whether it expects a single full-name field or separate first- and last-name fields.

Common Mistakes

  • Applying PROPER to every name without checking. It may alter intentional capitalization, particles, initials, or stylized spellings. Use the spacing-only formula when case should be preserved, and manually verify exceptions against a trusted source.
  • Assuming TRIM removes every invisible character. TRIM handles ordinary spaces but not every special space. The SUBSTITUTE with CHAR(160) in the formulas above addresses a frequent nonbreaking space, but data copied from websites or databases may contain other unusual characters.
  • Typing a formula over the source names. This can destroy the original information and make mistakes difficult to reverse. Put results in a separate column and keep a backup until review is complete.
  • Deleting every duplicate flag. Similar names may refer to separate people, and two rows with the same name may represent valid separate transactions or records. Investigate before removing anything.
  • Splitting names at every space. Names can contain multiple given names, family names, particles, or suffixes. A simple split can put part of a person’s name in the wrong field. Confirm the required format and inspect the affected rows before separating names.

Tips

  • Work on a copy and make one type of change at a time. It is easier to spot whether spacing cleanup or capitalization caused an unexpected result.
  • Use a small test range first. Try the formula on a few examples that include extra spaces, a nonbreaking space, a hyphen, an apostrophe, and a name with unusual capitalization before filling it through thousands of rows.
  • Keep the source and cleaned columns side by side during review. If the columns are far apart, it is harder to catch an unintended change.
  • When a name’s correct spelling matters, treat automated cleanup as a formatting aid, not a source of truth. Confirm special cases with the person or an authoritative record.
  • If names must be split, consider whether the data actually identifies first and last names. A single full-name field does not always contain enough information to split reliably, especially for names with several parts.

Troubleshooting

  • The formula appears as text instead of calculating: Check that the cell is not formatted as Text. Change the format to General, select the formula cell, press F2, and then press Enter. Also check that the formula starts with an equals sign and that Show Formulas is not turned on from the Formulas tab.
  • Extra gaps remain: The gaps may be nonbreaking spaces or other special characters. Use the formula that includes SUBSTITUTE(A2,CHAR(160),” “). If a gap remains after that, identify the source of the data and inspect a sample; a different character may need a targeted replacement.
  • The formula does not fill to the bottom: Drag the fill handle to the last row or copy B2, select the intended destination range, and paste. Confirm the last row number and look for blanks in the neighboring column that might have caused the fill handle to stop.
  • Names become incorrectly capitalized: Replace the PROPER formula with the spacing-only formula, or correct only the affected results manually. The formula’s case conversion is a general rule, not a reliable way to determine every person’s preferred spelling.
  • Excel shows an error or changes the formula separators: The examples use commas as argument separators, as requested for standard English-language Excel settings. Some regional settings use semicolons instead. If Excel rejects a formula, check the list separator configured for your locale and use the separator Excel expects.
  • You pasted values and now need the formulas again: Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if you have not made other changes. Otherwise, recreate the formula from the source column. This is why keeping the original names in a separate column is useful.

Frequently Asked Questions

Does TRIM remove spaces between names?
Yes. It reduces repeated ordinary spaces between words to a single space and removes ordinary spaces at the start and end. It does not remove every special whitespace character, which is why the examples also replace CHAR(160).

Will CLEAN remove all hidden characters?
No. CLEAN removes many nonprinting ASCII control characters, but it does not remove every character that might arrive from an imported file or website. If a particular character persists, it may need a specific replacement after you identify it.

Can I clean names without changing their capitalization?
Yes. Use the formula with TRIM, CLEAN, and SUBSTITUTE but without PROPER. It removes common unwanted spaces and control characters while leaving the letters’ existing case unchanged.

Can Excel split a full name into first and last name?
It can split text using Data > Text to Columns, but splitting on spaces can misplace parts of multiword names. Use that command only when the list’s naming pattern is consistent, and review the preview and resulting columns before replacing any data.

Can I remove duplicate names automatically?
Excel’s Data > Remove Duplicates command can remove repeated rows, but use it only after confirming which columns define a true duplicate and keeping a backup. Identical names alone do not prove that two records belong to the same person.

Do these formulas work in Excel for Mac or the web?
TRIM, CLEAN, SUBSTITUTE, CHAR, PROPER, and COUNTIF are standard Excel functions and are available in Microsoft 365 for Mac and Excel for the web. Ribbon placement and paste options can differ slightly, but the formulas themselves are the same.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.