How to easily remove blank spaces in Excel – Easy Guide 2021

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.

Remove Extra Blank Spaces in Excel - Problems caused by Extra Spaces
Problems caused by Extra Space

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

Syntax

TRIM(words)

you can substitute words with cell reference for which you want to use this function

Syntax

TRIM(cell_reference) 

Example

TRIM(A1) 
Remove blank spaces in Excel - Use TRIM function in Excel
Use of TRIM 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.

Syntax

Value(TRIM(cell_reference))

Example

Value(TRIM(A1))  

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.

Syntax

=SUBSTITUTE(cell_reference, " ", "")

Example

=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

Syntax

Value(Substitute(cell_reference))

Example

Value(Substitute(A1))  

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.

Use Paste special

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

Results after using Paste special

You can use any of above methods to remove spaces in Excel as per final output required by you.