How to remove Spaces between Characters and Numbers in Excel

In this post, we will show you how to remove spaces between characters and numbers in Excel. Whether you want to remove leading or trailing spaces or trim extra spaces between words and numbers in Microsoft Excel, this post will help you get rid of all unnecessary spaces that stick with your data while importing or copying-pasting it from external applications.

Extra spaces may sometimes appear with non-printable characters that may be hard to deal with. When you apply functions to such data, Excel counts these spaces as additional characters and shows incorrect results or throws errors. For example, if you compare two cells with the same content, the result may be incorrect if one of them consists of extra spaces.

Spaces may easily be recognized with the naked eye, but they too could be hard to spot in large data sets. In this post, we will guide you on how to remove these unwanted spaces using different methods.

How to remove Spaces between Characters and Numbers in Excel

The following methods will help you remove spaces between characters and numbers in Excel:

Remove spaces using the TRIM() function.
Remove spaces using the SUBSTITUTE() function.
Remove spaces using the Find and Replace feature.

Let us see these in detail.

Remove spaces in Excel using the TRIM() function

The TRIM() function is a text function in Excel that is used to fix irregular spacing. It removes all extra spaces from a given text string, leaving no spaces at the beginning and the end of the string and just a single space between the words of the string. When you’re dealing with textual data, using the TRIM() function to strip unwanted spaces would be helpful.

The syntax of the TRIM function is:

TRIM(text)

where text refers to the text string or reference to the cell containing the text string.

Let the understand this with the help of an example.

Suppose we have an Excel file containing the ‘Author Name’ and ‘Author Code’ for authors of TheWindowsClub as shown in the above image. The data consists of irregular spacing which needs to be fixed. For this, we may use the TRIM() function as follows:

Place your cursor on cell C5 and type the following function:

=TRIM(A5)

The above function will remove all unwanted spaces from the text string in cell A5 and display the result in cell C5. To copy the function to the entire column C, you may take your cursor to the bottom-right corner of cell C5 and as it turns to a plus (+) symbol, click, hold, and drag it till cell C9.

To use this method with your data, you will have to apply the function in a new column/cell and then copy-paste the results into the original column/cell. While pasting the data, make sure to select the Values (V) option in Paste Options.

Notes:

If your data consists of some non-printable characters, TRIM() function will not remove them. For this, you need to use the CLEAN() function. If the data consists of both extra spaces and non-printable characters, you may use a combination of both functions.
If you apply this formula to a number, it will remove leading and trailing spaces but limit the in-between spaces to 1. For removing all the spaces from numbers, you may use the next two methods.

Remove spaces using the SUBSTITUTE() function in Excel

SUBSTITUTE() is another text function that lets you replace an existing text with a new text in Excel. You may use this function to remove all the spaces (leading, trailing, and all in-between spaces) from a text string or a number.

The Syntax of SUBSTITUTE() function is:

Substitute (text,old_text,new_text,[instance_ num])

Where text refers to the main text string
old_text refers to the specific text that needs to be replaced with new_text
new_text refers to the text that should substitute the old_text
[instance_ num] is an optional parameter that refers to the occurrence of old_text that should be replaced with new_text. If this is not specified, all the occurrences of the old_text will be replaced.

Taking the above example, we may remove extra spaces between characters and numbers in Excel using the SUBSTITUTE() function as follows:

Place your cursor on cell C5 and type the following function:

=SUBSTITUTE(A5, " ", "")

The above function will replace all space characters with an empty string. Hence it will also remove the in-between spaces from the author names, as shown in the above image. Therefore it is best suited to remove spaces between numbers. Or in special cases may be used to remove spaces between words or characters.

Also, this function too will require you to apply it in a new column/cell. Once you get the results, you may copy-paste them to your original column/cell.

Read: Excel is slow to respond or stops working.

Remove spaces in Excel using the Find and Replace feature

The above results may also be achieved using the Find and Replace feature in Excel. As you may already know, Find and Replace is a handy feature that lets you replace a specific text with another text and is most commonly used for data correction, such as spelling mistakes. However, it can also be used to remove unwanted spaces, such as leading, trailing, or the extra spaces between numbers or characters in Excel.

The key benefit of using this feature is that it can work on a selected cell range or the entire worksheet in one go. So you don’t have to apply functions somewhere else and then copy-paste the results to the original cells. You can simply select the data range and use the feature. However, bear in mind that this will also remove the single space that separates words within a text string. So make sure you choose the appropriate method as per your requirement.

To remove spaces between characters and numbers in the above example, we may use the Find and Replace feature in Excel as follows:

Select the data range.
Click on the Find & Select dropdown in the top-right corner within the Editing toolbar.
Select the Replace option.
In the Find and Replace dialogue box, enter a space in the Find what field.
Do not enter anything in the Replace with field. Leave it empty.
Click on the Replace All button.

This will replace all the spaces with an empty string. Hence all the extra spaces will be removed from your Excel data.

Another important point to note here is the Find and Replace feature considers the leading zeros (0) as spaces. So it will remove all the zeroes from the beginning of your numbers.