How to Remove Space after Number in Excel (6 Easy Ways)

In this tutorial, we will demonstrate how to remove space after a number in Excel. While working on Microsoft Excel we are very likely to have additional spaces in our dataset. There may be multiple spaces between words or numbers, as well as leading and trailing spaces. If our dataset has extra spaces, several of Excel’s features may not function properly.


Download Practice Workbook

You can download the practice workbook from here.


6 Easy Ways to Remove Space after Number in Excel

In this article, we will explain 6 different ways to remove space after a number in Excel. To illustrate the process, we will use the following dataset for all the methods. The dataset contains the names of 4 products and their codes. We can see that product codes contain extra spaces after numbers. We will remove those spaces after numbers in 6 different ways.

6 Easy Ways to Remove Space after Number in Excel


1. Use TRIM Function to Remove Space after Number in Excel

First and foremost, we will use the TRIM function to remove space after a number in Excel. The TRIM function deletes all spaces from text. But, it doesn’t remove single spaces between words.

Let’s see the steps to perform this action.

STEPS:

  • To begin with, insert an additional column named ‘Cleaned Value’.

Use TRIM Function to Remove Space after Number in Excel

  • In addition, select cell D5.
  • Furthermore, type the following formula in that cell:
=TRIM(C5)
  • Press Enter.
  • So, the above action returns the value of cell C5 in D5 without spaces.

Use TRIM Function to Remove Space after Number in Excel

  • Lastly, drag the FIll Handle tool from cell D5 to D8. After that action, we get results like the following image. The new value of product codes doesn’t contain any spaces.

Read More: How to Remove Space in Excel after Text (6 Quick ways)


2. Delete Space after Number with Find and Replace Options

In this method, we will delete space after number in Excel using the Find & Replace options. Go through the following steps to do this method.

STEPS:

  • First, select cells C5 to C8.

Delete Space after Number with Find and Replace Options

  • Next, go to the Home tab.

Delete Space after Number with Find and Replace Options

  • Then, from the ribbon select the option ‘Find & Select’.
  • From the drop-down menu select the option Replace.

Delete Space after Number with Find and Replace Options

  • The above command will open a new pop-up window named ‘Find and Replace’. We can also press Ctrl + H to open that window.
  • After that, click on the ‘Find what’ field and press the Spacebar Also, click on the ‘Replace with’ field and press the Spacebar once.
  • Now, click on the ‘Replace All’ button.

  • Finally, we get results like the following image.

NOTE:

We can see that the Find & Replace options modify the source data. It removes spaces from the original values.

Read More: How to Remove Space between Rows in Excel (5 Methods)


3. Apply SUBSTITUTE Function to Remove Space after Number

In this method, we will use the SUBSTITUTE function to remove space after number in Excel. The SUBSTITUTE function in Excel substitutes a text in a given string with another specific text.

Let’s see the steps to use the SUBSTITUTE function for this method.

STEPS:

  • Firstly, insert an extra column named ‘Cleaned Value’.

Apply SUBSTITUTE Function to Remove Space after Number

  • Secondly, select cell D5.
  • Thirdly, write down the following formula in that cell:
=SUBSTITUTE(C5," ","")
  • Press Enter.
  • So, the above formula returns the value of cell C5 in cell D5 without spaces.

Apply SUBSTITUTE Function to Remove Space after Number

  • After that, drag the Fill Handle tool from cell D5 to D8.
  • Lastly, the above action returns all the value of product codes without space in cells (D5:D8).

Read More: How to Remove Blank Spaces in Excel (7 Ways)


Similar Readings


4. VBA to Remove Space after Number in Excel

Now, we will use a VBA code to remove space after a number in Excel. Just follow the below steps to apply VBA to delete space after a number.

STEPS:

  • To begin with, select cells (C5:C8).

VBA to Remove Space after Number in Excel

  • In addition, right-click on the active sheet named VBA. Select the option ‘View Code’.

VBA to Remove Space after Number in Excel

  • Furthermore, a new blank VBA code window will appear. Type the following code in the code window:
Sub Remove_Space()
Dim rng As Range
For Each rng In Selection.Cells
rng = Replace(rng, " ", "")
Next
End Sub
  • Now, click on the Run button or press the F5 key to run the code.

  • In the end, we get the result like the following image.

Read More: How to Remove Spaces in a Cell in Excel (5 Methods)


5. Remove Extra Space after Number with Power Query

In this method, we will use Power Query to remove space after a number in Excel. The main purpose of Power Query is data transformation. So, we can use a Power Query to complete this task. We will follow the below steps to do this method.

STEPS:

  • Firstly, go to the Data tab.
  • Secondly, from the ribbon select the option ‘From Table/Range’.

Remove Extra Space after Number with Power Query

  • The above action opens a new pop-up window named ‘Create Table’.
  • Thirdly, input the table range $B$4:$C$8 in the text field named ‘Where is the data for your table?’.
  • Keep the field ‘My table has headers’ unchecked.
  • Now, click on OK.

Remove Extra Space after Number with Power Query

  • The above command opens the power query editor.
  • Furthermore, right-click on the heading Column2. Select the option ‘Replace Values’.

Remove Extra Space after Number with Power Query

  • A new pop-up window named ‘Replace Values’ will open.
  • Now, we’ll need to add two spaces in the ‘Value To Find’ field and a single space in the ‘Replace With’ field, just like we did with the ‘Find & Replace’ approach.
  • Click on OK.

  • Lastly, we can see the values of product codes without spaces in the image below.

Read More: How to Remove Tab Space from Excel (5 Easy Methods)


6. Eliminate Space after Number Using Excel Delimited Option

In the last method, we will use the Delimited option to remove space after number in Excel. This method splits the data strings to remove space after a number in Excel. Follow the below steps to perform this method.

STEPS:

  • In the beginning, select cells (C5:C8).

Eliminate Space after Number Using Excel Delimited Option

  • Next, go to the Data tab. Select the option ‘Text to Columns’ from the Ribbon.

Eliminate Space after Number Using Excel Delimited Option

  • Then, a new pop-up window named ‘Convert Text to Columns Wizard’ appears.
  • Select the option Delimited in the ‘Choose the file type that best describes your data’ section. Click on Next.

Eliminate Space after Number Using Excel Delimited Option

  • After that, check the option Space as Delimiters. Click on Finish.

Read More: How to Remove Trailing Spaces in Excel (6 Easy Methods)


Conclusion

In conclusion, this tutorial will guide you on how to remove space after number in Excel. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment below. Our team will reply to you as quickly as possible. Keep an eye out for more inventive Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo