Quite often, we have data at our disposal that, although containing useful information, is not suitable for immediate analysis. One of the most common problems is the presence of extra spaces – at the beginning, at the end or inside between words.
This problem is treated very easily – a special function TRIM (TRIM). It removes all extra spaces, but leaves one space between words:
Simple and beautiful. But there are a couple of nuances.
Non-breaking spaces are not removed
A teaspoon of tar here is only the impossibility of removing non-breaking spaces in this way. This is a special variant of the space (outwardly indistinguishable from the usual one), on which there is no line break. It is usually used in cases like “Mr. Moscow” or “Ivanov I.I.” so that these phrases do not break between the lines. In Microsoft Word, such a special character is entered using the Ctrl + Shift + Space key combination and is displayed as a circle, not a dot:
Unfortunately, the TRIM function only removes regular spaces. To remove non-breaking ones, you need to use other functions or macros.
Formulas for removing leading and trailing spaces
If you need to remove only initial spaces (i.e., do not touch trailing and spaces between words), then you will have to do this with tricky formulas, because Unfortunately, there is no ready-made function (similar to the LTrim function in VBA) in Microsoft Excel.
The formula to remove extra spaces on the left only would look like this:
In English version =MID(A1;FIND(LEFT(TRIM(A1));A1);LEN(A1))
The formula for removing extra spaces only on the right will be a little more complicated and should be entered already as an array formula (with Ctrl + Shift + Enter):
In English it would be =LEFT(A1;MAX((MID(A1&REPT(” “;99);ROW(A1:A99),1);” “)*ROW(A1:A99)))
- Highlight cells with extra spaces with conditional formatting
- Cleaning up text from extra spaces, non-printing characters, English letters, etc.
- How to check if text matches a given pattern (mask)