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.

Overview of removing space after number


📁 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:

Sample dataset

Approach

1. Inserting TRIM Function

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.

=TRIM(C6)

Use TRIM Function in the 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.

Dragging Fill Handle

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

Result after removing irregular space after number

Approach

2. Incorporating SUBSTITUTE Function

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:
=SUBSTITUTE(C6," ","")

🔨 Formula Breakdown

SUBSTITUTE(C6,” “,””)

👉  The first argument specifies the cell from where the value needs to be substituted.

👉  The second argument is a space character that needs to be replaced.

👉  The third argument does not have any character. This argument is the newly replaced value.

Insert SUBSTITUTE Function in the formula

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

Drag the Fill Handle icon

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

SUBSTITUTE Function to remove space after number

Approach

3. Remove Space Using the Find and Replace Feature

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.

Find and Replace option

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

Find and Replace box

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

Find and Replace option to remove space after number

Approach

4. Remove Space After Number Utilizing Text to Columns

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.

Text to columns feature

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

Delimited option

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

Checking space

  • After that, we click on Finish.

Clicking 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:

Excel Delimited option to remove space after number

Approach

5. Use of Power Query Feature to Eliminate Space

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.

Create Table for power query

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

Select range for Table

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

Replace Values in Power Query Editor

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

Replace space using Power Query

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

Close and Load in Power Query Editor

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

Result after removing space in Power Query

Approach

6. Remove Spaces Applying VBA Code

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.

Opening Visual Basic Window

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

Insert new module

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

Running the VBA code

  • The results will come out as the following image.

VBA code to remove spaces


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:

Sample dataset

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.
=SUBSTITUTE(C6," ","")

Formula with SUBSTITUTE function to remove spaces before number

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

Dragging Fill Handle

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

Removing space before number


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

(Visited 49 times, 1 visits today)
Abrar

Abrar

Hello there! I am Mohammad Abrar Uddin, an engineering graduate from BUET. I write articles and blogs related to Excel in this site and hope that the viewers can learn simple to complex solutions of Excel problems easily by reading such articles.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo