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 problem to utilize 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.

Download Practice Workbook

4 Amazing Ways to Convert Text with Spaces to Number in Excel

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 the SUBSTITUTE function in two different ways.

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

1.1. Using SUBSTITUTE Function with Summation Rule

We can use the SUBSTITUTE function with the summation rule to convert text with spaces to number.

Steps:

• 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 SUBSTITUTE function 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

We can also use the SUBSTITUTE function with the multiplication rule to convert text with spaces to number.

Steps:

• 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

Read More: Excel VBA to Convert Textbox Value to Number (2 Ideal Examples)

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

Read More: How to Convert Text to Number with Excel VBA (3 Examples with Macros)

Similar Readings

• How to Convert Percentage to Whole Number in Excel (4 Methods)
• How to Convert Time to Number in Excel (5 Easy Methods)
• Convert 3 Letter Month to Number in Excel (8 Suitable Methods)
• How to Fix All Number Stored as Text in Excel (6 Easy Solutions)
• How to Convert Date to Week Number of Month in Excel (5 Ways)

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

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 (6 Ways)

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.

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. You can visit our Exceldemy site for more details on Excel.

Related Articles

• How to Convert String to Number in Excel VBA (3 Methods)
• Convert String to Long Using VBA in Excel (3 Ways)
• How to Convert String to Double in Excel VBA (5 Methods)
• Convert Percentage to Number in Excel (5 Easy Ways)
• How to Convert Date to Number in Excel (4 Methods)
• How to Convert Degrees Decimal Minutes to Decimal Degrees in Excel