➥ 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.

YOU CAN ALSO READ:   What is a UPS and how do you know if you need one?

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).

remove spaces excel 2

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.

remove spaces excel 3

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.

YOU CAN ALSO READ:   How to protect a Twitter account without paying for Blue

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.

remove spaces excel 4

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.

Write A Comment