How to Remove Space in Excel before Numbers (3 Ways)

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:

dataset for how to remove space in excel before numbers

Looks like normal values in the table. But, look at the value in the formula bar.

Space before numbers in excel

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)

Trim formula for removing space before numbers

STEP 2: Press ENTER. You can see the result.

values without space before numbers

STEP 3: Drag the Fill Handle (+ Sign) to copy the formula across the column. You will see all the values without Spaces.

Values without space before them

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.

Copy the value to check for spaces

STEP 5:  Paste the value in the new column. Now, check the formula bar. (Paste Values)

No spaces before numbers

 

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:

dataset for how to remove space in excel before numbers

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

substitute function to remove space

STEP 2: After that, press ENTER. You will see the numbers without any spaces.

Value with out space before number

 

STEP 3: Drag the fill handle (+ Sign) to copy the formula and see all the values.

Drag Substitute formula to remove space

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:

datset for find and replace prompt to remove space

Now, we are going to remove all those spaces.

STEP 1: Select all the data.

highlighting cells to remove space

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.

find and replace prompt

STEP 3: In the Replace With box, make sure there is no space in it. Keep this without any value. Then click Replace ALL.

Replace space in the prompt

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.

numbers without space before them

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.

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

ExcelDemy
Logo