Spaces in data can sneak into your data by way of typing mistake or during copy / paste of data. To remove blank space in Excel different methods can be used as per your requirement. These space can cause disaster to your excel calculations if not attended properly. Visually it doesn’t cause any difference but when calculating linked values it can give wrong result.
Below is one of the examples how this can impact. I have a list of student with their marks and if a leading or trialing space is entered it will not be visible to eyes. Space can also come between two words but this you can see if your attentive enough while checking Data.
Similarly if number in cell have space it again can result in wrong calculations.
Now Lets see how we can remove it
Remove blank spaces in Excel
Use TRIM function to Remove blank spaces in Excel which are leading, trailing or between words
Excel provided inbuilt function TRIM which can remove leading, trailing and multiple in-between spaces in one go. Best part of TRIM function is it will not impact any single space character between words.
Note: The TRIM function works with space characters, represented by code value 32 in the 7-bit ASCII character set. It will fail if used with Unicode space character set or Hard space.
Also result of trim function is text strings, so making any numerical calculation on values from trim function will yield wrong result. To Remove blank spaces in Excel which are leading, trailing or between words use below method
you can substitute words with cell reference for which you want to use this function
Above method is for useful for text but if you using it on number then your calculation will still be impacted, as TRIM function result is text string. To convert your number back from text to number using VALUE function of excel along with trim. Also if your content is text paste it to your reference cell using PASTE SPECIAL function to get your calculation correct.
Remove all space in Excel SUBSTITUTE function
Using Trim function will remove only extra space so if you want to remove all spaces use substitute function.
Here we will substitute all spaces with nothing.
=SUBSTITUTE(cell_reference, " ", "")
=SUBSTITUTE(A2, " ", "")
Again this method is for useful for text but if you using it on number then your calculation will still be impacted, as TRIM function result is text string. To convert your number back from text to number using VALUE function of excel along with substitute. Whether you are using TRIM function or SUBSITUTE function to Remove blank spaces in Excel, you will have to use one of the below methods if you are going to use these outputs in further calculations.
Convert text to number using VALUE function in Excel
Note: VALUE function can only be used when working with numbers.
Convert text output of you Excel formula to text using Paste special function in Excel
In case you are working with text and you still will have to convert result of TRIM or SUBSITUTE function to text. For converting your text back to text from formula you just need to copy the output and use paste special – paste value option.
To access paste special copy your content now right click on cell where you want to paste content and click on paste special followed by value then OK. Refer below image you can see your results are OK after using Paste special
You can use any of above methods to remove spaces in Excel as per final output required by you.