Many people can get frustrated because of the trailing or leading spaces that Excel may have.
You may not need these extra spaces, but it frequently gets into your Excel spreadsheets.
There are lots of reasons why you may end up with so many extra spaces.
It may be a part of the data you download from a database as you copy the data from a text document or manually enter it by mistake.
Once you have double, trailing, or leading spaces between texts, you may end up having so many terrible issues.
As an example, assume that you have this data set:
Check what would happen when you use the function VLOOKUP to use the first name to get the last name.
When you use your naked eye, it may be hard to see the difference.
What causes this error is the extra space trailing in the name.
Here in this example, spotting the issue in a minimal set of data is accessible.
However, imagine if you have to check this for more than thousands of records.
If you want to stay safe, we recommend removing the Excel spaces and clean up your data.
Use the TRIM Function
The TRIM function of Excel removes the double spaces and trailing and leading spaces in between the text strings.
In the example above, you have to remove the spaces from the list of first names in cells A2 to A7.
It is the formula that you need to put in Cell C1 so you can drag it down for every first name:
The TRIM function formula in Excel can immediately remove the cell’s trailing and leading spaces.
After cleaning up the data, have it coped and pastes as values in replacement for the original data.
It can be helpful to do this function whenever there is an excess of one space character in between the words.
With this, you can have additional spaces removed, leading to a result that always has a character space between the words.
The TRIM function of Excel can do a great job in having the Excel spaces removed.
Yet, it would fail every time your data set has non-printing characters like line breaks.
Remove the non-printing characters by using a combination of the functions CLEAN and TRIM.
Whenever cell A1 has some text where you want the spaces removed, this is the formula to use:
The formula =CHAR(160) can create non-printing characters that you may not be able to remove with the formula CLEAN.
Because of that, whenever you want to become sure that you have every non-printing character and extra spaces, this is the formula to use:
Use REPLACE and FIND to Remove Extra Excel Spaces
Allow yourself to remove the Excel spaces with the functionality of Find and Replace.
This technique is so much faster and can be helpful in these situations:
- Removing the line breaks
- Removing all of the space characters.
- Removing the double spaces.
Removing the Line Breaks
You can quickly remove any line breaks with Find and Replace with these steps:
- Choose the data.
- Go to Home –> Tap on Find and Select –> Select Replace or use the Keyboard Shortcut – Control + H.
- Once you get to the Find and Replace Dialogue Box:
- Look for What: Press Control + J (here, the only thing you may see is a blinking dot).
- Have this replaced with: Leave it empty.
- Replace All.
Choosing this can instantly have every line break removed from your chosen data set.
You can select either the find and replace or formula method to remove the Excel spaces according to your situation.
Removing the Single Spaces
These are the steps to follow to have all of the data set’s space characters removed:
- Choose the cells from where you want the space character removed.
- Check Home –> Go to Find & Select –> Select Replace. (Using the Control + H keyboard shortcut is also recommended).
- Check out the dialogue box Find and Replace and enter:
- A Single Space to Find What
- Leave it Blank to Replace With
- Tap on Replace All.
Doing this can remove every space character in your chosen set of data.
Take note that for this case, despite having more than a single space character between a couple of numbers or text strings, you can have everything removed.
Removing the Double Spaces
Take note that you can’t use this technique in removing trailing or leading spaces. You can look for and replace the double spaces, whatever their position may be. You can do this with these steps:
- Choose the cells with double spaces that you want to remove.
- Go to Home –> Choose Find & Select –> Click on Replace. (Using the keyboard shortcut Control + H is also possible).
- Look for the dialogue box Find and Replace and enter:
- Replace with: Single Space.
- Find what: Double Space.
- After that, click on Replace All.
With this, you can use a single space character to replace every double space.
Take note that you can only use this to remove the double spaces.
Whenever there are two words with three space characters, it will remove one and create two space characters.
For these cases, you need to repeat it to take away any double spaces that are left.