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.
Read More: How to Remove Space between Rows in Excel
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.
Download Practice Workbook
You can download the practice workbook from here.
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.
