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

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

## 1. Using 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â€™.

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

• 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

## 2. Deleting Space after Number in Excel with Find and Replace Tool

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.

• Next, go to the HomeÂ tab.

• Then, from the ribbon select the option â€˜Find & Selectâ€™.
• From the drop-down menu select the option Replace.

• 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 all spaces from the original values.

## 3. Applying 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â€™.

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

• 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

## 4. Running VBA Macro to Remove Space after Number

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

• In addition, right-click on the active sheet named VBA. Select the option â€˜View Codeâ€™.

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

## 5. Removing Extra Space after Number with Power Query Tool

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â€™.

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

• The above command opens the power query editor.
• Furthermore, right-click on the heading Column2. Select the option â€˜Replace Valuesâ€™.

• 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

## 6. Eliminating 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).

• Next, go to the Data tab. Select the option â€˜Text to Columnsâ€™ from the Ribbon.

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

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

• Finally, we can see the results in the following image. Column Câ€™s text strings are split into two It removes the space between strings as well.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
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

Advanced Excel Exercises with Solutions PDF