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.

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.

**Read More:** **How to Remove Tab Space from Excel (5 Easy Methods)**

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

**Read More:** **How to Remove Trailing Spaces in Excel (6 Easy Methods)**

**Similar Readings**

**How to Remove Space between Rows in Excel (5 Methods)****Remove Space in Excel after Text (6 Quick ways)****How to Remove Blank Spaces in Excel (7 Ways)****Remove Leading Space in Excel (5 Useful Ways)**

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