How to remove spaces in an Excel cell

You will learn how to use formulas to remove leading and trailing spaces in a cell, extra spaces between words, and get rid of non-breaking spaces and non-printing characters.

What's the biggest problem with whitespace? They are often invisible to the human eye. An attentive user may sometimes notice a blank space hiding in front of the text, or a few unnecessary spaces between words. But there is no way to visually detect trailing spaces, those that are out of sight at the end of cells.

Removing all spaces using Find and Replace
We use the formula SPACES
Removing leading and trailing spaces using the formula
How to remove line breaks and non-printing characters
Formula for removing non-breaking spaces
How to find and remove non-printable character
How to convert numbers with spaces to a number
Counting spaces using the formula
An easy way to remove spaces without formulas.

It wouldn't be a big problem if these extra intervals just existed, but they can affect the results of your formulas. The point is that two cells containing the same text with and without spaces, even if it is just one character, are considered different values. As a result, you may be left scratching your head trying to figure out why an apparently correct formula can't match two seemingly identical entries.

Now that you are fully aware of the problem, it's time to find a solution. There are several ways to remove spaces from a string, and this guide will help you choose the method that's most suitable for your specific task and the type of data you're working with.

How to remove spaces in Excel using Find and Replace

This is a faster method and can be useful in the following situations:

Remove double spacing.

Note that this method is not recommended for removing leading or trailing spaces, since at least one of them will still be there.

So, let's find and replace double intervals regardless of their location.

Here's how to do it:

Select the cells you want to remove them from.
Go to the main menu -> Find and select -> Replace. (You can also use the keyboard shortcut - CTRL + H).
In the Find and Replace dialog box, enter: Find: Double Space.
Replace with: Single.

Click Replace All.

Note that if you have three spaces between two words, you will now get two (one will be removed). In such cases, you can repeat this operation again to remove any double spaces that may still remain.

You can see the disadvantages of this method yourself: there is a leading space left before some of the words. Similarly, the trailing spaces after the text remain, they are just not clearly visible. Therefore, I would not recommend using this method for text expressions.

To remove all spaces in text , follow these steps:

Select the required cells.
Go to the Home menu -> Find and select -> Replace. (You can also use the keyboard shortcut - CTRL + H).
In the Find and Replace dialog box, enter:

Find: single space.

Replace with: Leave this field blank.

Click Replace All.

This will remove all spaces in the selected range.

It is hardly worth performing such manipulations with text data, but for numbers it is quite suitable. Intervals between digits often occur when importing data from other programs via a .csv file. True, in the figure you see that the numbers are still written in the form of text, but their appearance has become more orderly. How to turn them into real numbers will be discussed separately later.

Method 1: Replace Tool

If while working you accidentally put a double space in many cells, then using the “Replace” tool you can remove unnecessary spaces in an instant. Here's what you need to do to do this:

Open the desired table in the program.
Go to the "Home" tab.
On the tool ribbon, find and left-click on the “Find and Select” item.
In the menu that appears, select the “Replace” option.
In the window that appears, enter two spaces in the “Find” column by simply double-clicking the corresponding key on the keyboard.
In the “Replace with” column, enter one space.
Click the Replace All button.

After this, a report will appear showing exactly how many double spaces were corrected. Click "OK" to accept it. After this, you can see that all double spaces have been corrected and your document is restored to normal. This is the first way to remove spaces in numbers in Excel, let's move on to the next one.

SPACE function.

If your data set contains extra spaces, the SPACE function can help you remove them all in one fell swoop—leading, trailing, and a few in between, leaving just one space between words.

The standard syntax is very simple:

=SPACE(A2)

Where A2 is the cell you want to delete from.

As shown in the following screenshot, SPACES successfully removed everything before and after the text, as well as extra space in the middle of the line.

And now you only need to replace the values in the original column with the new ones. The easiest way to do this is to use Paste Special > Values.

Method 4: Using a special function

All of the methods described above are ineffective if you need to remove spaces in Excel that are at the beginning or end of a value. However, the SPACEBAR function copes with this flawlessly.

Place the cursor in the cell that is parallel to the column or row from which you want to remove spaces.
Open the Function Wizard window by clicking on the corresponding button located next to the function line.
In the “Category” list, select “Text”, and in the list with a list of functions, highlight “SPACEBAR” and click “OK”.
The function input window appears. In it you need to indicate the cell in which you want to remove spaces. To do this, simply click on it with the left mouse button.
Click OK.

Immediately after this, the extra spaces will be removed. However, the corrected version will be located in the cell where the formula was written. No problem, just copy the cell and paste it into the desired area.

Formulas for removing leading and trailing spaces.

In some situations, you may want to double or even triple space between words to make your data more readable. However, you need to get rid of the leading spaces (those at the beginning), like this:

As you already know, the SPACE function removes extra space in the middle of text lines, which we don't want in this case. To keep them intact, we'll use a slightly more complex construction:

=PSTR(A2,FIND(PSTR(SCAPES(A2),1,1),A2),LENGTH(A2))

This expression at the beginning calculates the position of the first character in the string. You then pass that number to another function TLST so that it returns the entire text string (the length of the string is calculated using LENGTH), starting from the first character position.

You can see that all the leading spaces have disappeared, although there are still a few spaces between words. As a final touch, replace the original text with the resulting values, as described above.

If you only need to remove spaces at the end of each cell, then the formula will be a little more complicated:

=LEFT(A2,MAX((PSTR(A2&REPEAT(" ";99),ROW(A2:A100),1)<>" ")*ROW(A2:A100)))

And please note that it must be entered as an array formula (with Ctrl+Shift+Enter). In Column A, the right alignment turned out poorly due to the different number of trailing spaces in each cell. Column B solves this problem and allows you to arrange the text nicely.

Method 3: Delete by formatting

If you type large numbers in table cells and see that after pressing Enter a space appears between the delimiters, then you have the appropriate formatting option. Now we will tell you how to remove spaces in numbers in Excel by turning it off.

Open the table in the program.
Select cells that have a space between numbers.
Right-click on the selection.
Select the “Format Cells” option from the context menu that appears.
In the window that appears, go to the “Number” tab.
Open the “Numeric” section located in the sidebar.
In the right part of the window, uncheck the box next to the “Digit group separator” line.
Click OK.

After this, the window will be closed, and you can see that all extra spaces have been removed from the document. As you can see, this is a fairly simple way to remove spaces in numbers in Excel, but it is worth considering that it only works in cases of text formatting.

How to remove line breaks and non-printing characters

When importing data from external sources, not only extra spaces appear, but also various non-printing characters, such as carriage return, line feed, vertical or horizontal tab, etc.

The SPACE function can get rid of spaces, but it cannot eliminate non-printing characters. Technically, it is designed to remove only the 7-bit ASCII value 32, which is the space code.

To remove non-printable characters from a string, use it in combination with the CLEAN function. As the name suggests, PECHSIMV is designed to clear data from unnecessary “garbage” and can remove any of the first 32 non-printable characters in the 7-bit ASCII set (values from 0 to 31), including line breaks (value 10).

Assuming the data being cleared is in cell A2, the formula would be:

=SPACE(PENCHIM(A2))

Almost always, when you remove line breaks using the above expression, the individual words end up “glued” together. You can fix this using one of the following methods:

Use Excel's Replace All tool: In the Find field, enter a carriage return by pressing Ctrl+J. And in the Replace field, enter a space. Clicking the Replace All button replaces all line breaks in the selected range with spaces.
Use the following formula to replace carriage returns (code 13) and line feeds (code 10) with spaces:

=SPACE(SUBSTITUTE(SUBSTITUTE(A2, CHAR(13), " "); CHAR(10); " "))

As you can see, the postal address in column C looks quite readable.

Formatting cells

Sometimes the cause of unnecessary spaces is formatting. It may already be indented or will move along with the data from an external source. To fix everything, you need to right-click on the top of the column and select the “ Format Cells ” tab there. A list of settings will appear; you need to select the “Number” tab, where you can configure the presence of bit gaps.

If you need to remove separating spaces in numbers, then go to the cell format settings and select “ Numeric ”. At the same time, uncheck the “ Digit group separator ” checkbox.

How to remove non-breaking spaces in Excel?

If after using the SPACE and PECHSYMB formula, some stubborn marks still remain, then most likely you copied/pasted data from the Internet or another program, and a few non-breaking spaces still crept into your table.

To get rid of non-breaking spaces (html code), replace them with regular ones, and then ask the SPACE function to remove them:

=SPACE((SUBSTITUTE(A2,CHAR(160);" ")))

To better understand the logic, let's look at the formula:

A non-breaking space has a code of 160 in 7-bit ASCII, so you can define it with CHAR(160).
The SUBSTITUTE function is used to convert non-breaking spaces into regular spaces.
And finally, you insert SUBSTITUTE into the SPACEBAR to finally remove all the unnecessary stuff.

If your worksheet also contains non-printing characters, in addition to what is described above, use the PRESSCHARG function to get rid of spaces and other unnecessary characters in one fell swoop:

=SPACE(SPESCHYMB((SUBSTITUTE(A2,CHAR(160));" "))))

The following screenshot demonstrates the difference in results:

About the dangers of extra spaces

The presence of extra spaces can interfere with work in the following cases:

You need to compare cells with each other. This can be either a search for duplicates within a range, or a comparison of two lists and a search for duplicate values between them
When building pivot tables, rows “collapse” if they are completely identical, and a difference of one space will not allow them to “collapse”. The algorithm for constructing pivot tables also compares cells with each other.
Using VLOOKUP or the INDEX/MATCH combination, you can “pull” data from one table to another. These functions also compare cells when searching and take into account the difference in the form of spaces.

Typically, extra spaces are not significant in text, and a popular task is to remove them from text. But remove the unnecessary ones so that the phrases do not stick together into one word.

How to remove a specific non-printing character

If the interaction of the three functions described in the example above fails to remove all the junk from the text, then it means that the remaining characters have ASCII values other than 0 to 32 (non-printing characters) or 160 (non-breaking space).

In this case, use the CHARCODE function to first identify the code of the offending character, and then use SUBSTITUTE to replace it with a regular space. And then use SPACEBAR to delete it.

Assuming that the unwanted characters you want to get rid of are in cell A2, you write two expressions:

In cell C2 determine the problem sign codeusing one of the following functions:

=CODE(LEFTCHAR(A2,1))

Trailing space or non-printing character at the end of a line:

= CODE(RIGHT(A2,1))

A space or non-printing character in the middle of a line, where n is the position of the problematic character:

= CODE(PSTR(A2, n, 1)))

In this example, we have an unknown character in the middle of the text, in the 11th position, and we will define its code:

=CODECHAR(PSTR(A2,11,1))

We get the value 127 (see screenshot below).

In cell C3, you replace CHAR(127) with a regular space (" ") and then simply delete it:

=SPACE(SUBSTITUTE(A2,CHAR(127), " "))

If your data contains several different non-printing characters, as well as non-breaking spaces, you can nest two or more SUBSTITUTE functions inside each other to remove all the unwanted characters at once:

=SPACE(SUBSTANCE((SUBSTITUTE(SUBSTITUTE(A2,CHAR(127);" ");CHAR(160);" "))))

The result should look something like this:

We used this universal formula. As you can see, it was successful.

How to remove all spaces

In some situations, you may need to remove absolutely all spaces in a cell, including those between words or numbers. For example, if you imported a numeric column into your table that uses spaces as thousand separators. Of course, place separators make large numbers easier to read, but they also make it difficult for your formulas to evaluate. These separators need to be created using formatting, not manually.

To remove all spaces in one fell swoop, use SUBSTITUTE as described in the previous example, except that you replace the space character returned by CHAR(32) with nothing (""):

=SUBSTITUTE(A2, CHAR(32), "")

Or you can simply enter it (" ") in the formula, like this:

=SUBSTITUTE(A2; “ “; “”)

The result of this will be text consisting of numbers. If you need numbers as a result, add two “-“ (minus) signs before the formula. Any mathematical operation automatically turns digits into numbers. And by applying minus twice, that is, multiplying by minus 1 twice, we will not change the value of the number and its sign.

Formula SUBSTITUTE

In addition to the first two methods, there is another method that uses a special formula “ SUBSTITUTE ”; if the English version is used, then “ SUBSTITUTE ”.

For the formula to work, you need to enter the following formula in the next column or line from where the numbers with spaces are located: =SUBSTITUTE(A1;" ";"") . The required address is entered in place A1. After this, if necessary, the formula is copied to all other cells and replaced in the same way as in the previous method. This way you can remove spaces from an entire column.