# How to Convert Text with Spaces to Number in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

We often need values in number format to apply them in various formulas and do other stuff too. In that case, if they are in text format, we will face some serious problems in utilizing them. For this reason, we have to convert the text format into number. In this article, I am going to explain 4 Amazing ways to convert text with spaces to number in Excel.

For more clarification, I am going to use a dataset with personal information in Name, City, and Phone columns.

## How to Convert Text with Spaces to Number in Excel: 4 Ways

### 1. Using SUBSTITUTE Function to Convert Text with Spaces to Number

We can convert text with spaces to number quite easily using the SUBSTITUTE function. Moreover, we can use this function in two different ways.

1. Using the SUBSTITUTE function with the Summation Rule
2. Using the SUBSTITUTE function with Multiplication Rule

#### 1.1. Using SUBSTITUTE Function with Summation Rule

• Create a new column to have the output. I have created a column naming Converted Output to have the result.
• Input the following formula to have the desired output.
`=SUBSTITUTE(D5," ","")+0`

Here, the formula emits the space in the value of cell D5. The reason for the addition is SUBSTITUTE converts any value to text, adding the value 0 will intact the number and the format to numeric.

• Press ENTER to have the result.

• Use Fill Handle to AutoFill the rests.

Thus, we can convert text with spaces to number in Excel.

#### 1.2. Using SUBSTITUTE Function with Multiplication Rule

• First of all, create a new column to have the output. I have created a column naming Converted Output to have the result.
• Then, Input the following formula to have the desired output.
`=SUBSTITUTE(D5," ","")*1`

Here, the SUBSTITUTE function emits the space in the value of cell D5.

• Hit ENTER to have the output.

• AutoFill the rest cells

### 2. Applying Text to Columns and CONCAT & VALUE Functions

Another very effective way to convert text with spaces to number in Excel is the application of the Text to Columns command followed by the formula with the combination of the CONCAT and VALUE functions.

Steps:

• Select all the cells that you want to convert to numbers. Here, I have selected cells D5 to D15 for that purpose.
• Go to the DataÂ tab.
• Click on Text to Columns from the ribbon.

• Next, check the Delimited â€“ Characters such as commas or tabs separate each fieldÂ box.
• Hit on the NextÂ button.

• Again, check the SpaceÂ box.
• Click on the NextÂ button.

• Check the TextÂ box.
• Finally, press the FinishÂ button.

Thus, We will have the value separated in different cells.

• Apply the following formula in the new column (i.e. Converted Output).
`=VALUE(CONCAT(D5:G5))`

Here, the CONCAT function combines the text from cells D5 to G5. Then, the VALUE function converts them to number.

• Press the ENTERÂ button.

• Now, AutoFill the remaining cells.

### 3. VALUE â€“ SUBSTITUTE Formula to Convert Text with Spaces to Number

The combination of the VALUE function and the SUBSTITUTE function can also be used to convert text with spaces to numbers.

Steps:

• Create a new column(i.e. Converted Output) to have the output.
• Next, input the following formula in the new column.
`=VALUE(SUBSTITUTE(D5," ",""))`

Here, the SUBSTITUTE function emits the space in cell D5. Then, the VALUE function converts them to number.

• Hit ENTER to have the output.

• AutoFill the rest cells.

### 4. Combining VALUE, SUBSTITUTE and CHAR Functions

We can also apply the combination of the VALUE, SUBSTITUTE, and CHAR functions to convert text with spaces to number.

Steps:

• Firstly, create a new column(i.e. Converted Output) to have the output.
• After that, input the following formula in the new column.
`=VALUE(SUBSTITUTE(D5,CHAR(32),""))`

Here, the CHAR function specifies a special number for every single text in cell D5. Then, the SUBSTITUTE function emits the space in cell D5. Then, the VALUE function converts them to number.

• Now, press ENTER to have the result.

• Use Fill Handle to AutoFill the rests.

## Practice Section

For more expertise, you can practice here.

## Conclusion

Thatâ€™s all for the article. In this article, I have tried to explain 4 Amazing ways to convert text with spaces to number in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below.

## Related Articles

<< Go Back to Convert Text to Number in Excel | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF