How to Remove Space in Excel Before Numbers (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Remove Space in Excel Before Numbers Dataset

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.


Remove Space Before Number Using TRIM Function in Excel

  • 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," ","")

Remove Space Before Number Using SUBSTITUTE Function in Excel

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

Utilize Find and Replace Option to Remove Space in Excel Before Numbers

NOTE: Here, I have copied the Product Codes into the Correct Code column for better representation.
  • 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.


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF