How to Remove Leading, Trailing Spaces in Entire Column Data

It is rather frustrating to have leading or trailing spaces in your column in Excel. Many serious issues could result from leading and trailing spaces between texts. Extra spaces in your worksheet can result from data downloaded from a database or copying data from text data. It also comes from entering data manually by mistake. Since it is easy to spot issues with a small data set whenever there are leading and trailing spaces in your worksheet, this may be difficult for many records after. Therefore, it is safe to clean your data to remove all leading and trailing spaces.

Table of Contents

Removing spaces in Excel

There are two ways to remove spaces in Excel. These include Using the TRIM function and Using the Find and Replace method.

Using the TRIM method

This function removes any leading or trailing spaces in the entire column of data. For example, to remove spaces from a list of names in a column in Excel, the following formula is used; TRIM( A2). Use in the following way

1. Create a new column after the one with the dataset

2. Apply by typing the TRIM( A2) formula in the column

3. Drag it down to your list; this cleans your data by removing the trailing and leading spaces

4. Copy and Paste in place the previous data

The TRIM function also helps when more than one space is contained between words. Extra spaces are removed, and the rest of the dataset will have one space character. Although this function is useful in removing space characters between words in columns, It does not work when there are line breaks and other non-printing characters. You, therefore, have to remove such using TRIM and CLEAN functions. In this process, you will use the following formula; =TRIM( CLEAN( A1)). Do the following;

5. Create a new column after the one containing the dataset

6. Apply by typing TRIM( CLEAN( A1)

7. Drag it down to your list, thus cleaning the entire data set from leading and trailing spaces

Line breaks and other non-printing characters can be a product of =CHAR(160). The CLEAN formula can still clear this. To confirm whether all leading and trailing spaces have been cleared, use the following formula; =TRIM( CLEAN( SUBSTITUTE( A1, CHAR(160)," ))). Use the steps given above.

Use FIND and Replace to remove leading and trail spaces

This technique is popular since it is faster and more efficient. It can be used when you want to remove double spaces and when you want to eliminate all other space characters.

For removing double spaces, Use these steps;

1. Select the cells that you want to apply this action

2. Click Home>Find and Select>Replace