Three ways to remove spaces in numbers in Excel

Gaps in numbers

Gaps in numbers can appear at any time, and if you work enthusiastically, you may not even notice their appearance. Among the main reasons it is worth noting the following:

when information from external sources is inserted into a cell, text fragments from the same source may also be inserted;
the numeric cell already has formatting , which provides for visual division into digits, this is intended for convenient reading of large numbers;
appearance of non-breaking spaces .

The usual “Space” can be removed quite simply, but the reasons described above cannot be easily eliminated. For them you need to use special formulas, scripts and formatting tools.

How to remove spaces in Excel?

Let's look at 2 main ways to remove unnecessary characters:

Find and Replace tool . Removing spaces via dialog box;
Using functions . Formulaic removal option.

Find and Replace Tool

Let's assume that we have text or a table of words that has duplicate spaces.

To begin, select a range of cells with data and select Home -> Editing -> Find and Select -> Replace or simply press the key combination Ctrl + H :

A standard replacement dialog box appears, then enter two spaces in the
Find and one space the Replace with Click Replace All and repeat the operation until Excel finds repetitions.
As you can see, the algorithm is quite simple, but this method of removing spaces does not work if they are present at the end or at the beginning of the text.

Using functions

In this case, you can use the standard SPACE , which removes all spaces, except single ones, between words.

Let's assume that we have a list of last names ( column A ) containing extra spaces both between words and at the beginning or end of the last name. Let's create an auxiliary column ( column B ), where we will enter the formula SPACES referring to the column with last names:

Next, copy the contents of column B and paste the unrelated values into column A, after which we delete the auxiliary column. The disadvantage of this method is its inconvenience when working with large amounts of data, since for each edited cell it is necessary to create an additional place where the original cell will be processed.

Non-breaking space in Excel

If, after performing these actions, the extra spaces still cannot be removed, then the text may likely contain non-breaking spaces.

This character looks like a regular space, but it cannot be removed using the SPACE function or the Find and Replace tool (when typed in the field to replace a regular space).

As you can see, cell A2 contains extra spaces, but the COMBINE SPACE function does not remove them.

Each character in Excel has its own encoding in the ANSI character table (for Windows OS), including the interesting single space (character 32) and non-breaking character (character 160).

Therefore, using the SUBSTITUTE function, we replace the non-breaking space (character 160) with a regular one (character 32), and then using the SPACE function to obtain the required result:

In fact, you can also remove a non-breaking space using “Find and Replace”; to do this, in the Find Replace with field empty.

The question arises, how to do this?

The first way is to directly copy the given character from the text and enter it in the Find .

The second way is to use the Alt code (for Windows systems) and enter the combination Alt + 0160 (hold down the Alt key and type 0160 on the numeric keypad).

Find and Replace

Find and Replace is the simplest possible method to remove spaces in an Excel cell. For everything to work out, you need to follow the following procedure:

Select a cell, column, row, or a number of them (where the spaces should be removed).
After this, you need to hold down the key sequence on the keyboard Ctrl+H . This key combination will bring up a special tool window called “ Find and Replace ” or “ Find & Replace ” (in the English version).
In the “ Find ” column you should enter a double space, and in the bottom “ Replace with ” only one. also set one space in the “ Find ” line and leave “ Replace with ” empty if you want to get rid of spaces altogether.
After that, all you have to do is click “ Replace All ”.

What to do if a sheet or workbook in Excel is password protected - how to remove the protection
A new window will appear on the screen, which will inform you about the replacements made and their quantity. To be on the safe side, you can do the last step again to make sure that all gaps in the file have been eliminated.

Method 2: Remove spaces between delimiters

If you set not a double, but a single space in numeric expressions, then the previous method will not help you. But you can make some adjustments to it that will help correct the current situation. To remove spaces in numbers in Excel, follow these steps:

Select the part of the table with numeric data that contains extra spaces.
On the tool ribbon, again click on the “Find and Select” button and select the “Replace” option. By the way, this action can be performed by using the hotkeys Ctrl+H.
A familiar window will appear where you need to enter two variables.
In the Find What field, enter a single space.
Do not touch the “Replace with” field, there should be nothing in it.
Click the Replace All button.

Just like last time, you will be shown a report that you can close by clicking OK. All spaces between numbers will be removed. This is a great way to remove spaces in numbers in Excel, but it is only effective when the spaces have been added manually. If they are installed automatically, then you need to use completely different instructions. This is what we will talk about next.

SPACE function

To remove spaces in Excel cells, they often use a special function called SPACES or TRIM (in the English version). It is somewhat more difficult to implement than the previous method, but much more effective and capable of coping with a more complex type of continuous indentation. For everything to work, you need to follow the instructions:

You should select the desired column or row in which you want to manipulate to remove unnecessary spaces and create another column or row next to it (opposite if it is a column and below if it is a row).
Now, in the cell of the newly created row or column, you should enter a function that looks like this: = SPACE (number of the adjacent cell where there are extra spaces) . In the English version, TRIM is inserted instead of SPACE.
Next you need to duplicate this function , simply extending it to all other cells.
The next step is to replace the first column (or row) with the one where the formulas are entered. To do this, you need to select all the cells with formulas, copy them (Ctrl+C), and then left-click on the first cell of the first column (or row).
, press the key combination Shift+F10 and V.

And in this way, unnecessary spaces between numbers or letters will be removed. The second column (or line) can be deleted.

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.

Ways to insert a table from Microsoft Excel into Word

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.

Remove spaces only on the left or right

If you need to remove spaces only on the left or right (otherwise known as leading and trailing spaces), you need to do the following:

Remove space on the left at the beginning of the line. For everything to work, you need to enter the following formula in the adjacent cell: =PSTR( A1, FIND(LEFT(COMPLACE( A1))) . If the English version, then: =MID(A1,FIND(LEFT(TRIM(A1)),A1 ;LEN(A1)) Where A1 is the target value to be processed.
Remove space to the right at the end of the line. In the adjacent cell you need to enter the following formula: =LEFT(F1;MAX((PSTR( A1&REPEAT(“ “;99);ROW(A1:A99);1);" “)*ROW(A1:A99))) . In the English version: =LEFT(A1;MAX((MID(A1&REPT(" ";99);ROW(A1:A99),1);" ")*ROW(A1:A99))) . Where A1 is the target value to be processed.

Microsoft Excel

Many errors come from something you can't even see: white space. Consider the example shown in Fig. 198.1. Cell B2 contains a formula that looks for the name of the color in cell B1 and returns the corresponding code from the table: =VLOOKUP(B1,D2:E9,2,FALSE).

Rice. 198.1. A simple search formula returns the color code entered in cell B1

In Fig. 198.2 The formula in cell B2 returns an error indicating that red was not found in the table. Thousands of Excel users would spend a lot of time trying to figure out why this happened. In this case, the answer is simple: cell D7 does not contain the word Red. More precisely, it contains the word Red followed by a space. For Excel, these lines are completely different.

Rice. 198.2. The search formula cannot find the word Red in the table

If a worksheet contains thousands of text entries and you need to perform a comparison, you will first need to find the cells that contain excess spaces and then correct them. The term "excess white space" means a text entry that contains any of the following: one or more leading spaces, one or more trailing spaces, or two spaces together.

One way to identify this type of cell is to use conditional formatting. To set conditional formatting, follow these steps:

Select all the text cells to which you want to apply conditional formatting.
Select Home ► Styles ► Conditional Formatting ► New Rule the New Formatting Rule dialog box .
At the top of the window, select Use a formula to determine format cells.
Enter a formula similar to the following at the bottom of the dialog box: =A1SPACE(A1). Note that this assumes that cell A1 is the top-left cell in the selection. If this is not the case, replace the top left cell address with the set of cells you selected in step 1.
Click the Format to open the Format Cells and choose a format type for cells containing excess spaces. For example, set the highlight to yellow.
Click OK to close the Format Cells , and click OK to close the Create Format Rule .

After completing these steps, every cell that contains excess spaces and is within the range you selected in step 1 will be formatted in the way you choose. You can easily identify these cells and remove spaces.

Because of the way the SPACE , the formula in step 4 also applies conditional formatting to all numeric cells. Here's a slightly more complex formula that doesn't apply formatting to numeric cells: =IF(NOT(UNTEXT(A1)),A1SPACE(A1))