How to clean up messy text in Excel: TRIM, CLEAN, SUBSTITUTE and more

Spreadsheet tools · Published September 27, 2026

Remove extra and invisible spaces, fix capitalization, strip unwanted characters and split text into columns, so lookups and filters stop failing.

The short answer

Put a cleaning formula in a helper column, check the results, then paste them over the originals as values. For most messy text, this one formula does the job:

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

SUBSTITUTE turns non-breaking spaces (common in text copied from web pages) into normal spaces, and TRIM removes spaces at the start and end and shrinks runs of spaces to one. Wrap it in PROPER, UPPER or LOWER to fix capitalization as well.

Remove extra spaces with TRIM

1. Insert an empty column next to the messy one.
2. In the first row, type =TRIM(A2) and press Enter.
3. Double-click the fill handle to copy it down.

TRIM takes one argument, the text to clean. It keeps single spaces between words. Extra spaces are hard to see, so =LEN(A2), which counts characters, is a quick way to prove they're there.

Non-breaking spaces from web pages

If TRIM seems to do nothing, the space is probably a non-breaking space (character 160). Microsoft's documentation says TRIM removes only the ordinary space (character 32). On its own, =TRIM(A3) above only drops the final space and still returns "james chen" with the non-breaking space inside. Replace it first:

=TRIM(SUBSTITUTE(A3,CHAR(160)," "))

SUBSTITUTE takes the text, the character to find (CHAR(160)) and what to put instead (a normal space). If that doesn't work on a Mac, where CHAR uses a different character set, use UNICHAR(160) instead (Excel 2016 and later).

Line breaks and other invisible characters: CLEAN

=CLEAN(A2) removes non-printing characters (codes 0 to 31), such as tabs and line breaks from exported systems. It deletes them without adding a space, so "Unit 5" and "Main St" on two lines become "Unit 5Main St". To keep the words apart, swap line breaks for spaces instead:

=TRIM(SUBSTITUTE(A2,CHAR(10)," "))

Fix capitalization

• =UPPER(A2) gives MARIA LOPEZ.
• =LOWER(A2) gives maria lopez, which suits email addresses.
• =PROPER(A2) capitalizes the first letter of each word: Maria Lopez.

Remove specific characters with SUBSTITUTE

To delete a character, replace it with empty quotes. =SUBSTITUTE(A2,"SKU-", "") turns SKU-00123 into 00123. For several characters, nest one SUBSTITUTE inside another. This strips the brackets, dash and space from a phone number:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")," ","")

"(555) 010-2345" becomes 5550102345. The result is text, which is what you want for phone numbers and IDs, as it keeps any leading zeros.

Fix numbers stored as text

Numbers stored as text are usually left-aligned, often show a small green triangle in the corner, and are ignored by SUM. Three fixes:

1. Convert to Number: select the cells, click the warning icon next to them and choose Convert to Number.
2. Text to Columns: select one column, choose Data › Text to Columns and click Finish straight away. Excel re-enters every value, turning numbers into numbers.
3. VALUE: =VALUE(A2) turns the text "1,250.00" into the number 1250 (with US number settings). If there are stray spaces, use =VALUE(TRIM(A2)).

Split text into columns

Text to Columns (every version)

1. Select the column. Make sure the columns to its right are empty, or pick another destination in step 4.
2. Choose Data › Text to Columns, pick Delimited and click Next.
3. Tick the separator, such as Comma or Space, and check the preview.
4. Set the Destination cell, then click Finish.

Flash Fill

Type the first result by hand next to the data, then press Ctrl+E (or Data › Flash Fill). Excel fills the rest from your example. The results are plain values that won't update, so check a few rows.

Formulas

For "Last, First" names, these work in every version:

=LEFT(A2,FIND(",",A2)-1)

=MID(A2,FIND(",",A2)+2,100)

FIND returns the position of the comma. LEFT takes everything before it (one character fewer). MID starts two characters after the comma, skipping the comma and space, and takes up to 100 characters, which is simply "the rest".

In Microsoft 365 and Excel 2024 (Windows and Mac), there are shorter options. =TEXTBEFORE(A2,", ") returns Lopez, =TEXTAFTER(A2,", ") returns Maria, and =TEXTSPLIT(A2,", ") spills both parts into two cells. In older versions they show #NAME?. TEXTBEFORE and TEXTAFTER return #N/A when the separator isn't in the cell, and FIND returns #VALUE!.

Replace the originals with the clean values

1. Select the helper column with the formulas and copy it.
2. Right-click the top cell of the original column and choose Paste Special › Values (or the Values icon under Paste Options).
3. Delete the helper column.

Pasting values first matters: delete the source column while the formulas still point at it and they turn into #REF! errors.

When the mess comes from a CSV file

Opening a CSV in Excel can change values before you start: leading zeros disappear and some codes turn into dates. To see what the file really contains, open it in Filecon's CSV viewer, which shows values exactly as written. To work on it in Excel without those changes, convert it with CSV to Excel and choose "Everything as text". Both run in your browser, and the file isn't uploaded.

Troubleshooting

• Lookups still fail after TRIM: check for non-breaking spaces or line breaks, and compare =LEN() of both values. Then see VLOOKUP and XLOOKUP.
• #VALUE! from VALUE: the text contains something other than a number, such as a currency word, or uses a decimal comma your settings don't expect.
• Text to Columns overwrote data: press Ctrl+Z, then choose an empty Destination.
• Leading zeros vanished: the column became numbers. See keeping leading zeros.

In short

Clean in a helper column with TRIM, SUBSTITUTE and CLEAN, fix case with PROPER, UPPER or LOWER, split with Text to Columns or formulas, then paste the results back as values. Clean data also makes the next steps easier, such as combining text and finding duplicates.