How to Remove the Space Characters in the Penultimate Positions in Excel Cells

In the process of copying and pasting, there will sometimes be space characters in cells. In this article, we will show you an example of the space characters in the penultimate positions.

When you are working on an Excel worksheet, you will sometimes meet with problems. One of the problems is that there will be additional space characters in cells. You will certainly need to remove those useless spaces. If you remove spaces one by one, you will spend a lot of time and energy. But on the other hand, the space characters will be in the same position, such as the penultimate positions. Therefore, you can use the methods below to quickly remove the space characters.

Remove Space Characters in the Penultimate Positions

In the image below, you can see the space characters in cells. All the spaces are in the penultimate position. And here we will use formulas to solve the problem.Example

  1. Click a blank cell that you need to input the result. As an example, we will click the cell B1.
  2. And then input this formula into the cell that you have selected:

=SUBSTITUTE(A1,LEFT(RIGHT(A1,2),1),””,3)

In this formula, the cell A1 contains the space that you need to remove. Below we will analyze the formula in detail.

  • RIGHT(A1,2): It will return the last two characters in the target cell. And for cell A1, the characters are “ r” (There is a space in the two characters).
  • LEFT(RIGHT(A1,2),1): It will extract the first character of the result by the RIGHT function. Therefore, this function will return the space character.
  • SUBSTITUTE(A1,LEFT(RIGHT(A1,2),1),””,3): By using the SUBSTITUTE function, this formula will remove the 3rd space from this cell.

With this deep analysis, you will know that the next time when the space is in another position, you only need to modify certain arguments in the formula.

  1. Next press the button “Enter” on the keyboard. You will see the result in cell B1.
  2. After that, double click the fill handle of the cell B1 in the worksheet. Therefore, all the cells will be filled with the same formula. In addition, the space characters in the penultimate position will be removed.Result

Actually, except for the above formula, you can also use this formula in cells:

=SUBSTITUTE(A1,MID(A1,LEN(A1)-1,1),””,3)

This formula will calculate the length of the value in cell A1. And in the formula, the space character in the penultimate position will be replaced with null character. With this formula, you can also remove the space characters.

In your actual worksheet, when the space characters are in different, you can also change certain arguments in formula. By using formulas, you can save a lot of time and energy on removing space characters in certain positions.

Excel Still Has Vulnerabilities

Even though Excel is very useful for your work, you still need to take care of your files. Sometimes Excel will still corrupt due to many different reasons. At this moment, you can use a powerful tool to repair corrupted xls file. Hence, you don’t need to worry about the vulnerabilities in Excel application.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc document damage and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.