How to remove blanks in Excel?

Working with Excel is undoubtedly super great, it saves you so much work, since it allows us to import the information that we have saved, than doing it manually, you would waste a lot of time. One of the jobs you can do in Excel is a database. In it you could empty customer information, such as names and surnames, available emails, telephone numbers, among others. But perhaps, after doing so, you will realize that there are some blank spaces , either at the beginning, in the middle or at the end of the information.

The detail of these blank spaces is that perhaps you come to think that there are no problems with them, and therein lies the drawback, the reality is that there are. And the problems that you will surely have is that these spaces can modify the results . Hence our interest in ´How to eliminate white spaces in Excel´, which precisely in this article, we are going to explain how to do it.

Index( )

Why is it important to know how to remove spaces from our Excel?
What functions can be used to remove whitespace?
1. Spaces
2. REPLACE
3. TRIM
4. Using VBA code
How can I remove whitespace in Excel with macros?
1. At the beginning of the data group
2. In the middle of your data set
3. At the end of the text string

Table of Contents

Why is it important to know how to remove spaces from our Excel?

We all like to see an excellent end result of every job that is done. And this is no different, when we talk about working with texts in Excel. This magnificent spreadsheet automatically gives you a result, after you have dumped any information into it. But there is a very common and nasty error that occurs in Excel, and it is blanks . These, as already mentioned, can appear at the beginning, in the middle or at the end of the text.

It should be noted that these blank spaces are very difficult to perceive. And the point is that it is ‘ important to know how to remove spaces from our text in Excel’, because these would affect the ordering of the cells and they become different, interfere in the calculation and there are many inconveniences when using search jobs as it is likely to have no results. For these reasons, in this article we are going to explain in detail the functions that can be used to eliminate these blank spaces and also do it with macros, so keep reading.

What functions can be used to remove whitespace?

There are several ‘ functions you can use to remove whitespace’ . The first function is manually, this means that you will be eliminating the blank spaces ´cell by cell, data by data´, so in this way you can change that space in your text that you don’t like anymore. time that would harm the result of your work. It should be noted that this function will only be possible if you have few data reports.

The second function you can use is the ‘SPACES function in Excel’ . In this function, it is the one that is applied when we have a ´BBDD in Excel´, this means that it corrects hundreds or thousands of reports with blank spaces. The third function is ´SUBTITUTE´, the fourth function is ´RECORT´ and the fifth and last function is ´Using VBA code´. We will explain all these functions below step by step.

Spaces

We already have the inconvenience, there are already those blank spaces that are present after having emptied a long data information in your Excel sheet. Now, it only remains to use the functions to be able to eliminate them. The first Function that at this point we are going to use is that of ‘SPACES’. This is mostly used when spaces are at the beginning of the cell. We will give you an example of how to remove that space.

Let’s imagine that in Cell A2 there is Pedro’s name, but before him, there are two spaces, to eliminate them you must apply the following formula: = SPACE (A2). As you can see, it is a very easy formula, without so many details, but it will eliminate the spaces that are present at the beginning of the cell, be it one, hundreds or thousands as explained above.

REPLACE

The second Function that we are going to use at this point is ‘SUBSTITUTE’. This is mostly used when spaces are in the middle of a cell. We will give you an example of how to remove that space. Let’s imagine that in Cell A2 there is the text Madrid Spain and we want to eliminate the space that separates the words so that they remain continuous or united. To eliminate them you must apply the following formula: = SUBSTITUTE (A1; “”; “”) . Madrid Cundinamarca Spain. He will paste the three words separating the two spaces that separate the texts.

It is important to mention that the quotes that are at the beginning , investigate the vacant spaces, and for this reason, they are spaced, and the second quotes replace the vacant spaces with the information that exists between the quotes. It should be noted that this last case is nothing, for that reason the quotation marks do not have any separation and there is nothing in the middle of them.

This can be done for any set of white spaces that are in the middle of the cell. However, the SUBSTITUTE function still removes spaces at the beginning of the cell, in the middle, and at the end of each word. This could have the disadvantage that you will lose the known of each of the text strings, which will make it difficult for you to recover the original data or perhaps never recover it.

TRIM

The third Function that at this point we are going to use is that of ‘CUT’. This is mostly used when spaces are at the beginning and end of a text string. This function is super simple because it reveals those parts where there are two or more blank spaces and removes them, later compressing them to just one, between each of the sentences. It is important to mention that if you install Office 2010 SP1 on your PC, the name of the CROP function will automatically change to the name SPACES.

Now, although this ‘CUT’ function is very simple, it takes too much time to eliminate trailing spaces when it is in many cells. That is why in the next topic, we are going to present you the best function that will remove these inconveniences as it is using the VBA code.

Using VBA code

The fourth Function that we are going to use at this point is that of the ‘VBA code’. This function is mostly used when spaces are at the end of a text string. Most of all when these text strings are many. Therefore, the ‘VBA code’ supplies a macro to remove all those trailing spaces from the cells that are selected, even if they are a large number.

To eliminate the blank spaces ´Using the VBA code´ you must follow these steps: First, choose the cell in which you want to eliminate the trailing spaces by means of the range. Second, click on the ALT + F11 keys , in this way you will open the Microsoft Visual Basic for applications store . After that, in the options that appear, click on the so-called box, other choices will slide, choose the one that says Module and proceed to paste the following macro in the module window. Macro: 1) Sub NoSpaces (), 2) ‘Update by Extendoffice 20180613, 3) Dim c As Range, 4) For Each c In Selection.Cells, 5) c = Trim (c), 6) Next, 7) End Sub.

After having pasted the previous macro in the module window, click on the F5 option on your computer key , this will be used to make this macro . Quickly, all trailing spaces will be removed from the cells of the select range, resolved.

How can I remove whitespace in Excel with macros?

Before knowing how to eliminate blank spaces in Excel with macros, you must be clear that Macros is used in those cases that we do the same job repeatedly in Excel, the good thing is that Excel allows you to enable and disable macros. This means that what Macros does is record that job, so that it is automatically executed for you, without having to start from scratch. This saves and records even the clicks we make with the mouse and the touches we make to the keys of our device.

It should be noted that if you wish at any time to make a small modification to your Macros, you can do it without any inconvenience. Even so, it is always the case that we need to eliminate the blank spaces in Excel, but this time, it will be with Macros. Therefore, here we bring you three ways to do it , depending on your need. The first is, removing spaces ‘At the beginning of the data group, the second in the middle of your data set and the third At the end of the text string’.

As an example, we are going to place the one that you need to report to the director of the administrative area on a monthly basis. And in this report, you want the names of the customers with unpaid invoices to be in yellow and highlighted in bold. In this case, the most feasible and recommended thing is that you create and run a Macros, where it will automatically make these changes on a monthly basis to the cells you have chosen.

In case you find it very embarrassing to have to set these three Macros mentioned to eliminate spaces in a text, let’s use the forward-looking format , especially for those who have to do it every day. The good thing that at this point, we must already have the Macros created, so what we are going to do is relate them in a dependent way and we will save them in the template that we use to make our monthly reports.

In the Excel spreadsheet, look for an option titled Developer , go there and choose the Macros selection , and then the Insert command, which lets you add a button to the Excel spreadsheet. Proceed to create three buttons, which will serve one for each Macros, for that reason it is important to know how to enable and disable macros in Excel sheets on our PC. In case you proceed to create them directly, it will ask you, if you want to set a Macros, in this case, choose the Start option and then close it. Repeat these steps with the other two buttons, but setting the other two Macros. You do this by clicking with the right option above each button, and proceed to transform the name so that you can identify them.

Finally, assign the button to Excel choosing the spaces of the cells that you want to delete, one by one , and press the option of the Macros that you want to establish. It is important that you know that if you save them in the template that you use routinely, it will save you much more time and headaches. If you want to create a Macros with the previous three functions in 1 , to save more time, when removing spaces from the text string, you must follow these simple steps. In the Excel spreadsheet, look for an option titled Programmer, go there and choose the Macros selection, choose the Macro name,which will be Sub DeleteTotal (), then you must follow and click on these normative requests to choose Macros names such as: Dim cell As Range, For Each cell In Selection, cell.Value = LTrim (cell.Value), cell.Value = RTrim (cell.Value), cell.Value = WorksheetFunction.Trim (cell.Value), Next and click on the Create button.

At this point, we must be inside VisualBasic , so here it proceeds to write the code for you to save the Macros. As a last option, click Sub and End. In Sub, it is the place where each of the Macros are saved. The first Sub needs the name of the Macros, after this, add the second Sub with the second Macro, and so on repeatedly. You must bear in mind that when you set any of the Macros, you will no longer be able to undo them, so when creating and setting the Macros, you must make sure you do it very well. So follow below, its step by step in detail.

At the beginning of the data group

When the Macros you want to implement is to Eliminate the spaces at the beginning of the data group of the text string, you must follow these simple steps. In the Excel spreadsheet, look for an option titled Programmer, go there and choose the Macros selection, choose the name of our first Macros, which will be Sub DeleteInicio (), then you must follow and click on these normative requests to choose Macros names as they are: Dim cell As Range, For Each cell In Selection, cell.Value = LTrim (cell.Value), Next and click on the Create button.

At this point, we must be inside VisualBasic , so here it proceeds to write the code for you to save the Macros. As a last option, click Sub and End. In Sub, it is the place where each of the Macros are saved. The first Sub needs the name of the Macros, after this, add the second Sub with the second Macro, and so on repeatedly.

In the middle of your data set

Now, if the case is that the Macros you want to implement is to Eliminate the spaces In the middle of your data set from the text string, you must follow these simple steps. In the Excel spreadsheet, look for an option titled Programmer, go there and choose the Macros selection, choose the name of our second Macros, which will be Sub Eliminate Intermediates (), then you must follow and click on these policy requests to choose Macros names as they are: Dim cell As Range, For Each cell In Selection, cell.Value = WorksheetFunction.Trim (cell.Value), Next and click on the Create button.

At the end of the text string

And as the last Macros, which we have to implement in case you need it, it is to Eliminate the spaces at the end of the text string, you must follow these simple steps. In the Excel spreadsheet, look for an option titled Programmer, go there and choose the Macros selection, choose the name of our third Macros, which will be Sub DeleteFinal (), then you must follow and click on these normative requests to choose Macros names as they are: Dim cell As Range, For Each cell In Selection, cell.Value = RTrim (cell.Value), Next and click on the Create button.

Once you have done the steps mentioned above, let’s go back to the example we gave, and start examining the Macros that you have established. You can do it economically or proactively . If it is economically, you should not do anything, we will simply call the Macro when we need it. And we do this by selecting the text where we want to cut and we establish the Macro ´Programmer / Macros / Select macro / Execute´. It should be noted, that before setting the Macro, select the range of Excel cells that you want to clean . Remember that we must establish these excess spaces one after another, until we leave the text as we wish.