Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we can remove space before numbers easily in Excel for better operation. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you 3 easy methods of how to remove space in Excel before numbers. Hence, read through the article to learn more and save time.
Often, we need to remove space before numbers for certain business analytics, and the process becomes more interesting with Excel. However, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. For the purpose of demonstration, I have used the following sample dataset. Here, the dataset contains 5 Product Names and Product Codes. However, you can see that product codes contain extra spaces before the numbers. Hence, I will remove those spaces before numbers and show them in a new column named Correct Code.
1. Using the TRIM Function to Remove Space Before Numbers in Excel
Basically, the TRIM function removes space before and after numbers. Moreover, it removes the space if present at the end and also from the beginning. But we think you should also learn this function so that in the future you can also use this to remove space from the end also. Hence, read through the steps below.
📌 Steps:
- In the beginning, select cell D5 and write down the following formula.
=TRIM(C5)
- In the end, press Enter and utilize the AutoFill tool to the entire column.
Read More: How to Remove Tab Space from Excel
2. Inserting Excel SUBSTITUTE Function to Delete Space Before Numbers
Furthermore, the SUBSTITUTE function is mainly used to replace any number of characters in a string. In addition, we can use it to remove space before numbers too. However, it will be a useful option to do that and the process is quite easy. Hence, go through the steps mentioned below.
📌 Steps:
- Initially, select cell D5 and insert the following formula.
=SUBSTITUTE(C5," ","")
- Lastly, hit the Enter key and apply the AutoFill tool in order to get the final result.
3. Applying Find and Replace Option to Remove Space Before Numbers
Last but not least, you can remove space before a number by utilizing the Find and Replace option. However, you can find any space easily and also can replace them with no space. Hence, read through the steps below in order to complete the task properly.
📌 Steps:
- Firstly, select the data where you want to remove space before numbers.
- Secondly, press Ctrl + H on your keyboard and it will open the Find and Replace option.
- Thirdly, in the Find What option, enter a Space.
- Fourthly, in the Replace With box, make sure there is no space in it. Now, keep this without any value.
- Afterward, click on Replace All.
- Finally, the output will appear as in the image below.
Read More: How to Remove Space after Number in Excel
💬 Things to Remember
- First of all, you have to create an extra column in order to get results for methods 1 and 2.
- Then, you can not only remove the spaces before numbers but also remove spaces between and after numbers using the given methods.
- Next, you need to copy the data into another column if you want to see the changes side-by-side for method 3.
- After that, you can apply the Find and Replace option in order to remove spaces for multiple rows and columns at a time. For this purpose, do not select any particular column and follow the rest of the steps.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Conclusion
These are all the steps you can follow to remove space in Excel before numbers. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
Related Articles
- How to Remove Extra Spaces in Excel
- Remove Space Before Text in Excel
- How to Remove the Trailing Spaces in Excel
- How to Remove White Space in Excel
- How to Find and Replace Space in Excel
- How to Remove Space between Rows in Excel
- Remove Space in Excel after Text
- How to Remove Blank Spaces in Excel
- How to Remove Leading Spaces in Excel
