6 Approaches to Remove Space After a Number in Excel

We often have to work transferring a lot of data from external sources to a spreadsheet. In doing so, our data can get mixed up with some unwanted extra spaces. This makes our data unusable and disorganized. To get rid of such extra spaces from our data, we can use multiple features of Excel. In this article, we will talk about how to remove space after a number in Excel using 6 different approaches. Here is a brief overview of the methods, outputs, and formulas used to remove space after a number in Excel.

📁 Download Excel File

Download the practice workbook below.

Learn to Remove Space After Number in Excel with These 6 Approaches

To demonstrate things easily, we will consider a dataset where we have different companies and their Product IDs consisting of numbers and alphabets. The Product IDs have spaces in between the numbers and the texts. We will remove unwanted space after a number in Excel with the help of the following approaches. The sample dataset is shown as follows:

TRIM function is useful when we have to remove irregular spacing. This function removes all the spaces of the data entry except the single space between two separate words. So, this function will keep a single space between two words. The steps for this approach are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, we select cell D6 and write down the following formula.

• Then, we press Enter. We will see that there will be a single space between the numbers and the text.
• After that, we drag the Fill Handle from the bottom of cell D6 up to cell D12.

• Thus, we will get all the outcomes of the Product IDs with a single space between the numbers and the text groups. All other irregular spaces will be removed. Look at the image below for the outcome.

The SUBSTITUTE function substitutes a new value in place of an old value. Using this function, we will remove the space substituting the initial space character with a non-space entry. The steps we need to follow in this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell D6 and write down this formula in the formula bar:

• After that, we press Enter.
• Look at the Fill Handle at the bottom right of cell D6 and drag it up to cell D12.

• We will see that the outcomes will not have any spaces at all. The results will look like the following image.

The Find and Replace option is available in the Editing group under the Home tab. We can use this editing tool to get rid of empty spaces as well. To do so, we need to follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we copy the contents of cells C6:C12 to cells D6:D12.
• Now, we select cell range D6:D12 and then we go to the Home tab.
• Then, we select the Find and Select option from the Editing group and click on Replace.

• A window will pop up with the name Find and Replace.
• In the Find What box, we enter a space, and in the Replace With box, we don’t enter anything.
• Then, we select the Replace All box and close the Find and Replace box.

• The spaces in the data will be removed and the outcome without spaces will look like the following image below.

In this approach, we will be using the Delimited option under Text to Columns feature which enables us to split data based on the availability of space in the data and other ways. To do so, we will follow these steps.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select the cell range C6:C12.
• We select the Text to Columns feature from the Data tab.

• A pop-up window will appear with the name Convert Text to Columns Wizard. In that window, we select the Delimited option and press Next.

• Among the Delimiters, we check the option space and uncheck all other options. Then we click on Next.

• After that, we click on Finish.

• The entries in the C column will split into two or more columns if there is any intermediate space between the characters. This is also another way of removing spaces and thereby splitting data. The results will look like the image below:

The Power Query is the data automation tool found in Excel. We can use this approach to generate an output table free from spaces like the methods mentioned above. The steps for this approach are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• First, we go to the Data tab.
• Then we select the From Table/Range option.

• A popup box will appear named Create table. We select the table range, check the option My table has headers, and press OK.

• The Power Query Editor will load and appear. We right-click on the Product ID column and select Replace Values from the Context Menu.

• The Replace Values window will appear. In the Value To Find box, we enter a space character. In the Replace With box, we enter nothing and press OK.

• We will see that there is no space in the Product ID column between the characters.
• Then, we click on Close and Load.

• A new sheet with the Power Query Table will appear as shown in the following image.

Implementing a VBA Code is the most fruitful solution in the case of working with large data. To write a VBA code, we follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, we select cell range C6:C12 and right-click on the VBA Code sheet.
• Then, go to the View Code option from the Context Menu.

• Then, we click on the Insert tab and select Module. A new Module will be created.

• After that, we write the following VBA code on the right window.

Sub Eliminatingspace()
Dim R As Range
For Each R In Selection.Cells
R = Replace(R, " ", "")
Next
End Sub

• Then we press the Run button or use the Function key F5.

• The results will come out as the following image.

How to Remove Spaces in Excel Before Number

Now, let us consider an alternative circumstance where we have unwanted spaces before numbers in our data entries. We will look at one of the ways in which we can remove spaces before numbers in Excel. We will use the SUBSTITUTE function to solve this issue. The dataset is as follows:

Like the approach shown before, we will use a similar approach of the SUBSTITUTE function to get rid of unwanted space before numbers. To do so, we need to follow the steps below:

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell D6 and write down the following formula.

• Then, we drag the Fill Handle at the bottom of cell D6 up to cell D12.

• We will see that the output data will have all the spaces removed before the numbers.

📄  Important Notes

🖊️  The TRIM Function does not remove the single space in between words or strings.

🖊️  The Delimited option splits data whenever there is a space in the entry data.

📝  Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌  We can remove spaces between numbers and texts in Excel using functions like SUBSTITUTE and TRIM. TRIM function, however, doesn’t remove the single spaces between words or text strings.

📌  We can use Data Tools such as the Text to Columns feature that splits data into multiple cells whenever any space is found in between texts.

📌  We can also use the Editing tools like Find and Replace option which removes unwanted spaces from the existing data field.

📌  To work with a large volume of data, we can also write a VBA code to remove spaces after a number in Excel.

Conclusion

To conclude, I hope that this article has helped you to understand how to remove space after a number in Excel. If you have any queries, reach out to us by leaving a comment below. Follow our website ExcelDen for more informative articles related to Excel.