Remove Spaces from a String

This post will guide you how to remove extra spaces between words or text string from the specified range of cells in excel. How to delete all spaces from excel cells. For example, when you paste data from an external source to your current worksheet, and you must want to delete all the extra spaces, such as: leading spaces or trailing spaces, or you just want to remote all spaces between words.

Remove All Spaces from a String

If you want to remove all blanks or spaces in range of cells B1:B4, you can create a formula using SUBSTITUTE function to achieve the result. Like this:

=SUBSTITUTE(B1," ", "")

Type this formula in the Cell C1, and press Enter key, then drag the AutoFill Handle over the cells that you want to apply this formula. You will see that all spaces have been removed.

remove spaces1

Remove All Spaces from a String

If you want to remove extra spaces from text string in cells, you just need to use the TRIM function to remove extra leading space or trailing space or other extra spaces. Type the following formula in cell C1, and press enter key, then drag the AutoFill Handle over the cells that you want to remove extra spaces.

=TRIM(B1)

remove spaces2


Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….

Related Formulas

  • Remove Leading and Trailing Spaces
    To remove leading and trailing spaces from text string in Cells, you can use the TRIM function to remove extra spaces.If you want to quickly remove leading and trailing spaces, you can write an  Excel VBA macro code…

Leave a Reply