➥ Go to the beginning of the free online Excel Course
- Removing spaces between characters and numbers in Excel is key to harmonious presentations
- What are the ways we can remove spaces between characters and numbers?
- We analyze three functions that could be of enormous help in this type of case
If you already have a few weeks of training with Excel and little by little you have mastered its basic features, you are probably at the right time to incorporate new knowledge. If you want your spreadsheets to be perfect, we will teach you, in a simple way, how to eliminate spaces between characters and numbers.
Indeed, in this article we are going to show you how to remove spaces between characters and numbers in Excel. Regardless of whether the spaces to be removed are located at the beginning or end of each cell.
The need to learn how to do this is because sometimes extra spaces can appear for no reason. It is almost always more difficult to investigate the reasons why they appear than to get rid of them in seconds. Paying attention to spaces, especially invisible ones, is key to the results of your spreadsheets.
Ways to remove spaces between characters and numbers in Excel
Using the TRIM() function
The TRIM() function is a text function in Excel that is used to correct irregular spaces like these. Removes all extra spaces from a given text string, leaving no trailing spaces and honoring only one space between words in the string. When dealing with textual data, it’s useful to use the TRIM() function to remove unwanted spaces, so we think that’s the option to start with.
The syntax of the TRIM function is:
TRIM(text)where text refers to the text string or a reference to the cell that contains the text string.
If we wanted to remove spaces between characters and numbers in cell A3, it would look like this:
TRIM(A3)
The above function will remove all unwanted spaces from the text string in A3 and if you want to copy this function to the entire column A, just move your cursor to the bottom corner, to the + symbol and press. Immediately drag it to cell A12, for example, and all spaces will be removed.
On the other hand, if some of your data is non-printable, the TRIM() function will not remove it. Yes CLEAN(text).
Using the SUBSTITUTE() function
SUBSTITUTE() is another text function that allows you to replace existing text with new text in Excel. You can use it to remove all spaces between characters and numbers, though it is somewhat more complicated.
SUBSTITUTE(text, old_text, new_text, [núm_instancia])
If we wanted to replace existing text with new text in Excel in cell A3, it would look like this:
SUBSTITUTE(text, » «, «», [A3])
The previous function will replace all space characters by removing those spaces permanently. It will absolutely remove all spaces in between author names, between elements, etc.
Using the Find and Replace function
The above results can also be achieved using the Find and Replace function in Excel. find and replace is a handy tool that allows you to replace a specific text with another text. Although usually used in data correctionlike misspellings, has other uses.
The key benefit of this feature is that it works on a selected cell range or the entire sheet. Obviously, that will save you a lot of work having to scroll the function all over the sheet.
- Select the data range, and click the Search and select dropdown menu in the upper right corner
- Already in the editing toolbar, click on the Replace option but do not write in the box
- Press the Replace All button and this will replace the shaded spaces with an empty string
One important detail to keep in mind is that the Find and Replace function considers leading zeros (0) as spaces, so it will remove them along with all other spaces, so keep an eye on that.
In any case, it is clear that it is possible to get rid of all spaces between characters and numbers. Forget complex tutorials or doing it manually, because this is the best way to achieve it.
