A clean data is our birthright, but sometimes we are not able to access it.
Sometimes, we are not able to use our data as the way we want.
And one of the biggest reasons which make our data unable to use is...
So if you want to use it we need to remove them all.
When you think about this, most of the time when you get data from ERP systems or you have some formulas which not written properly, you get those unwanted white spaces.
...now let's get started.
It happens sometimes that you get trailing and leading spaces in your data and it looks very nasty.
But we have a very simple and easy method to pull out this garbage out and you need only one function to deal with it.
It removes all unwanted spaces from a text string except the spaces between two words, look at the below example.
As you can see, we have used TRIM to remove all the unwanted trailing and leading spaces from the cell.
There are total 70 characters in the cell and when you have used TRIM, characters count is decreased to 41, that means it has deleted the 29 spaces from the cell.
There is also another possibility that you’ll get a line break in a text string.
In this situation, you need to use combine CLEAN and TRIM where the CLEAN function can remove all non-printing character which includes a line break as well.
First of all, CLEAN removes all non-printing characters from the text string and then TRIM removes all the unwanted spaces from text string other than space between two words.
Here we have another type of issue with these white spaces.
It happens sometimes that we want to remove all type of spaces from a cell. But clean and trim both are not able to remove all type of spaces.
For example, if you have a non-breaking space in a cell, you will not able to deal with it using both of these functions.
And for this problem, you need to combine CLEAN and TRIM with SUBSTITUTE.
Let me show you an example.
In below example, we have a list of mobile numbers in a worksheet and these numbers are surrounded by some unwanted white spaces.
We only need 10 digit mobile number from it.
SUBSTITUTE helps you to find a specific character or a set of characters from a text string and replace with some other characters or a single character.
Alert: Use this method only when you are confirmed to remove all type of spaces from selected cells.
Extra spaces are always irritating.
But now you have 4 different methods to remove them and you can choose any of these methods which you think is perfect for you.
I hope this will help you to play well with your data and now you need to tell me one thing.
Do you know another way to deal with this problem?
Please share with me in the comment box, I'd love to hear from you and don’t forget to share this tip with your friends.