How to remove space at the end of a cell in an Excel spreadsheet

Sometimes an Excel user has a problem: unnecessary spaces are shown at the beginning and end of a cell or incomprehensible spacing between words appears. Their main problem is that they are often invisible to humans. Therefore, he often does not suspect anything, especially if the eye is sufficiently blurred by large amounts of data. The problem is that there is no way to visually detect the presence of extra spaces at the end of cells in the “part of text” display mode. Today we will take a closer look at what can be done in order to remove all the gaps.

How to remove all spaces at the end of cells using Find and Replace

This method can be used in some situations, because it is characterized by a special speed and simplicity.

Removing double spacing

If there is a space at the beginning or end of the cell, it is not recommended to use this method. All because one of them will still be. However, let’s see how to use this method to remove double spaces:

1. Click on the cell where you want to remove unnecessary spaces. You can also select a whole range of values.
2. Open the main menu. There is a group of tools “Editing” and there you need to click on the “Find and Select” button. After that, a small menu will appear in which you need to select “Replace”. It is also possible to use a combination of hot keys CTRL+H.
3. In the window that appears, you need to enter a double space in the “Find” field and replace it with a single one.

After we enter all the necessary data, you just need to click on the “Replace All” button. But clicking on the “Replace” button is not recommended, because it replaces only one error, and you need to click it many times to fix it.

There are also more difficult situations. For example, a user has three spaces in a cell, so doing exactly all of the above will leave two spaces, since the method described earlier removes only one gap. Therefore, it is recommended to perform this operation several times so that the remaining spaces are removed.

You can understand the disadvantages of this method for yourself. There is a leading space before some words. Likewise, some trailing spaces are still visible, they just can’t be seen by eye. Therefore, if the expression is text in a cell, it is undesirable to use this method.

Removing all spaces in text

It may also be necessary to remove all spaces. If this is to be done, then the following steps must be followed:

1. Select the cells with which we will perform this operation.
2. Open the “Home” tab, and there we click on the “Replace” button as we did in the previous example. The keyboard shortcut is the same.
3. Enter the following data in the window:
   1. Find: single space.
   2. The field “Replace” is not filled.
4. After that, click on “Replace All”.

This method allows you to remove all spaces that are in the cell. The disadvantage is also understandable. Using this method, you can also remove the necessary spaces that are placed between words. Therefore, before using it, you need to make sure that all cells in the range contain no more than one word. For this reason, this method is best suited for numeric operations. 

In some cases, there may be unnecessary spaces between digits. Very often this happens when the user is trying to import information from another program. The cell can also be in text format. At this stage, this is not a problem, and then we will figure out what to do in order to change the format to numeric.

How to remove spaces in cells using TRIM formula

There is also a special function designed to remove unnecessary spaces. It’s called TRIM. It makes it possible to remove them at a time, both initial and leading. She is also able to substitute intermediate ones under the knife. After it, only one interval remains, so you need to be careful to a certain extent. The syntax for this function is: =TRIMSPACES(A2).

In parentheses, you must specify the cell from which we will remove spaces. So, as you can see in the following picture, this operator was able to easily remove all spaces at the beginning and end of the string written in the cell, as well as unnecessary spaces within the string.

Next, you need to replace the values ​​contained in the original column with the updated ones. The easiest method to do this is Paste Special. There you need to select the item “Values”.

How to remove spaces at the beginning and at the end of a cell with formulas

As for only spaces at the beginning and end, there are special formulas that can be used to remove them. This screenshot clearly shows the formula we used to achieve this goal.

As you understood from the above, the function TRIM not suitable for removing spaces only at the beginning and end of a line. To leave the middle spaces intact, the formula will be much more complex and include several operators at once, as we can see from the screenshot above.

It works like this: it determines where the first character of a text string is located. After that, this number is taken as an argument by the function PSTR, which then passes the entire string, starting at the first position.

In turn, the operator DLSTR determines how big a particular string is. After we have entered this formula, we check the result. If the gaps have disappeared, then we did everything right. Only as a final step, it is necessary to replace the original text with the values ​​that we received above.

If we only need to remove trailing spaces, then we need to use an even more complex formula: =ЛЕВСИМВ(A2;МАКС((ПСТР(A2&ПОВТОР(» «;99);СТРОКА(A2:A100);1)<>» «)*СТРОКА(A2:A100))).

Important: This formula must be entered as an array formula. That is, you need to use the key combination Ctrl + Shift + Enter. We were not very good at right-aligning the value in the first column, because the number of spaces at the end of each line was different. In the second column, we can see that we managed to solve this problem and the text was nicely positioned.

How to remove line break and non-printing characters

During the transfer of information from other programs, non-printable characters often appear that look like a space or a little differently, but they take up space in the cell. These are various carriage returns, line feeds, tabs, and so on.

The previously described function TRIM does a great job of removing spaces from a cell, but it’s not good at clearing it of non-printable characters. In fact, it is only needed to remove the character with code 32 in the 7-bit ASCII table. This is how this symbol is encoded. But there are also values ​​with other codes for which you need to use the function PRINT. In the English localization of the program, it is called CLEAN. As we can understand from the name, using this operator, you can clear the cell of all kinds of trash and remove the first 32 non-printable characters.

Let’s say we need to remove both spaces and non-printable characters in cell A2. In this case, our task is to use first the formula PRINT in order to remove non-printable characters, and then pass this line to the function TRIM, which removes the remaining spaces. We see the result of using this combination of Excel operators in the screenshot.

Essentially, every time you remove line breaks using the above method, the words separated by them are glued together. How to fix this situation?

1. Open the Find and Replace dialog box using the method described above. In the “Find” field, specify the carriage return character. To do this, enter the key combination Ctrl + J. In turn, in the “Replace” field, use the space character. When we click the “Replace All” button, all breaks are automatically replaced with spaces.
2. You can also replace carriage returns with spaces using this formula: =TRIMSPACES(SUBSTITUTE(SUBSTITUTE(A2, CHAR(13),” “), CHAR(10), “”)) . Naturally, you need the address of the cell in the function SUBSTITUTE replace with the one that is needed specifically in your situation.

As you can see, now the problem with the postal address has been successfully resolved.

Removing non-breaking spaces

Very often the user who uses the function TRIM or PRINT finds they don’t work. This is because there is another non-printable character that stands for non-breaking spaces. Very often, such characters appear when a user tries to insert information from the Internet directly into an Excel spreadsheet.

To do this, you need to replace the non-breaking space character (it has the code 160) with a space using the function SUBSTITUTE. After that, the gap is removed in the same way as before. If it turns out that the worksheet also contains non-printable characters, then the following formula must also be used: =TRIMSPACES(CLEAN((SUBSTITUTE(A2,CHAR(160),” “)))).

How to find and remove a non-printable character

But what if we already know what specific non-printable character is bothering us? Or is the character we need to delete not within the first 32 in the ASCII table? In this case, you need to use the function SUBSTITUTEto replace it with a space.

If we do not know the code of the non-printable character that we need to remove, then we need to use the function KODSIMV, which allows you to get it. Moreover, we do not need to use the functions KODSIMV и SUBSTITUTE separately, they can be immediately used in one formula so that the computer first determines the code of the interfering character, and then writes it down as an argument SUBSTITUTE.

The very structure of using the function in our case is quite complicated. In order to get the character we need, we need to use the operators LEVSIMV, RIGHT or PSTR. Let’s take a closer look at what and when to use:

1. If the incomprehensible non-printable character that we need to remove is at the very beginning, we must use the following formula: =Character(LEFTCHAR(A2))
2. If it is located at the end of the line, the formula is similar, but a different operator is used: = SYMCODE(RIGHT(A2))
3. If it is in the middle of the line, then we need to use the function PSTR, where the value of n is the character number where the problem character is located.

The combinations can be the most bizarre. For example, using this formula, the user can determine the desired character codes, replace them with spaces, and immediately destroy them. And all this in one formula: u2d TRIM SPACES (CLEAN CHAR ((SUBSTITUTE (SUBSTITUTE (A127, CHAR (160), ” “), CHAR (XNUMX), ” “)))).

Convert digits with spaces to a number

Sometimes it may be necessary to remove even those spaces that are used to separate the digits of numbers. Of course, they make it much easier to read large numbers, but they also prevent formulas from working correctly. Therefore, you cannot use spaces as separators, you need to use formatters for this. To do this, use the “Cell Format” menu, which is called using the context menu.

How to count spaces using formulas

Sometimes the user needs to determine the total number of spaces in the formula. To do this, you need to perform the following formula: =LSTR(A3) – LSTR(SUBSTITUTE(A3;” “;””)). If we need to determine how many unnecessary characters are in a cell, we need to use the following formula: =LONG(A3)-LONG(TRIMSPACES(A3)).

As you can see, it is not difficult at all to remove unnecessary spaces from the table, even if they are hidden or in a non-standard position. As for removing one or more spaces that are at the beginning or end of a line, this is a completely trivial task.