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

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.

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.

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

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.

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.

Read More: How to Bulk Convert Text to Number in Excel

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.

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.

Read More: How Excel Formulas Convert Text to Number

Practice Section

For more expertise, you can practice here.

Download Practice Workbook

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

• How to Convert Green Triangle to Number in Excel
• How to Fix Convert to Number Error in Excel
• How to Fix All Number Stored as Text in Excel
• Excel Convert to Number Entire Column
• How to Convert Alphabet to Number in Excel

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