Shanto
Space between numbers is a common situation while working on Excel. Sometimes, they are intentional. Sometimes, it is a mistake. We give input numbers in a general format but we want to view them as a number. But, spaces between them don’t give us that view. In this tutorial, we are going to show you how to remove space in Excel before numbers.
Download Practice Workbook
3 Ways to Remove Space in Excel before Numbers
We are going to show you 3 ways to remove spaces. In our opinion, these 3 formulas will work efficiently to remove those spaces. Two of them are functions and the other one is using the Find & Replace option.
1. Remove Space before Number Using TRIM Function
Now, basically, the TRIM function removes space before and after numbers. It removes the space from the end and 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.
🔗 Read More: How to use the TRIM function in Excel.
Basic TRIM formula:
=TRIM(Text)
Look at this Dataset:
Looks like normal values in the table. But, look at the value in the formula bar.
There are spaces before the numbers. Now, we are going to remove that using the TRIM function.
STEP 1: Create a column “Without Spaces”. Now in the first cell type this formula:
=TRIM(B3)
STEP 2: Press ENTER. You can see the result.
STEP 3: Drag the Fill Handle (+ Sign) to copy the formula across the column. You will see all the values without Spaces.
STEP 4: Now, we have to check if there is any space before the number. First, create a new column. Copy any value from Without Spaces.
STEP 5: Paste the value in the new column. Now, check the formula bar. (Paste Values)
You can see there is no space before the number.
2. Remove Space Using the Substitute Function
Now, the SUBSTITUTE function is mainly used to replace any number of characters in a string. We can use it to remove space before numbers too. It will be a useful option to do that. To know more about the SUBSTITUTE function read the article “How to use SUBSTITUTE function in Excel (4 Examples)”
Basic SUBSTITUTE formula:
=SUBSTITUTE (text, old_text, new_text)
Look at this dataset:
Here you can see some numbers with spaces. We are going to remove that using SUBSTITUTE.
STEP 1: Create a column Without Spaces. Select the first column and type the formula:
=SUBSTITUTE(B3," ","")
STEP 2: After that, press ENTER. You will see the numbers without any spaces.
STEP 3: Drag the fill handle (+ Sign) to copy the formula and see all the values.
As you can see, there is no space before numbers.
3. Remove Space before numbers Using Find & Replace Prompt in Excel
The final and easy way to remove space before a number is using the Find and Replace prompt. You can find any space easily and also can replace them with no space.
Look at this dataset:
Now, we are going to remove all those spaces.
STEP 1: Select all the data.
STEP 2: Now, press Ctrl + H on your keyboard. It will open the Find and Replace prompt. In the Find What option, enter a Space.
STEP 3: In the Replace With box, make sure there is no space in it. Keep this without any value. Then click Replace ALL.
It will open another prompt showing how many changes were made. Click OK. You will see, all those values are in there without any spaces.
Conclusion
Removing the spaces with these formulas will work efficiently and save you a lot of time. You can easily implement those in any rows or columns in any worksheets. Make sure you understand those ways and practice them accordingly. Our workbook is about to give you a basic idea. Download the practice workbook and give it a try. Be sure to check our website Exceldemy.com for Excel-related articles to enrich your knowledge.
