Quickly Clean Up Text in Excel with TRIM

[nerdslife_team]

When you’re pulling text into Excel from other places, you often get stuck with extra spaces. These can mess up your data if you’re trying to sort, analyze, or run formulas. Luckily, Excel’s TRIM function is your friend here, designed to get rid of those pesky spaces.

The TRIM function cleans up spaces at the beginning and end of text and also squeezes multiple spaces between words down to a single space. It works for the standard space character (ASCII 32) but won’t touch non-breaking spaces (character 160) that you sometimes find when copying from web pages. Here’s how to use TRIM to tidy up your data.

Using TRIM to Remove Extra Spaces

The TRIM function is super simple to use:

=TRIM(text)

text can be the actual text in quotes, or a cell containing the text.

Let’s say you’ve got a worksheet with text that has leading, trailing, double, and more extra spaces, and want to clean it up.

1. Select an empty cell where you want the clean text. For example, choose cell B1.
2. Type =TRIM(A1) into cell B1, this applies the TRIM function to the text in cell A1.

Now, cell B1 shows the cleaned up version of the text from A1 with the extra spaces removed.

You can also use TRIM directly on a text string by enclosing the text within quotation marks like this:

Applying TRIM Across Multiple Cells

If you have a whole column of text to clean, you don’t have to do it one by one. You can drag and apply the function to other cells.

1. After you’ve used TRIM on the first cell (like B1), find the small green square at the lower right corner of the cell (the fill handle).
2. Click and drag this handle down to the rest of the cells where you need the same function to be used.

This copies the TRIM function, and the same operation is done on the text in other corresponding cells in the original column.

Method 1: Removing Leading Spaces Using TRIM with REPLACE and FIND

Sometimes, you may want to remove only the leading spaces and keep any other spaces in the text, such as addresses with double spaces between parts. Consider an address that has double spaces between different parts but also has unwanted leading spaces. Using TRIM by itself would remove all extra spaces, not just the leading ones. To remove only the leading spaces, you need to combine TRIM with other functions.

Here’s the formula:

=REPLACE(A1,1,FIND(LEFT(TRIM(A1),2),A1)-1,"")

Here’s what the formula is doing step-by-step:

• TRIM(A1) gets rid of all extra spaces in the text of A1
• LEFT(TRIM(A1),2) grabs the first two characters of the trimmed text.
• FIND(LEFT(TRIM(A1),2),A1)-1 finds the position of the first non-space character in the original text, and subtracts 1.
• REPLACE(A1,1,...,"") removes leading spaces by replacing them with an empty string.

Method 2: Removing Non-Breaking Spaces using TRIM and SUBSTITUTE

The standard TRIM function doesn’t remove non-breaking spaces (character code 160) that you might find when copying text from websites, although these spaces are often invisible, they can cause issues.

To remove these, use TRIM combined with the SUBSTITUTE function.

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

Here’s a breakdown:

• SUBSTITUTE(A11,CHAR(160)," ") changes non-breaking spaces to regular spaces in cell A11.
• The outer TRIM() function then removes any extra spaces, including the newly added spaces, that may have been caused by the substitution.

Using TRIM and combining it with other Excel functions is an efficient way to clean up text data and ensure accurate spreadsheets.