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 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.
Excel Convert Text with Spaces to Number


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.
=SUBSTITUTE(D5," ","")+0

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.

 Excel Convert Text with Spaces to Number

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.
=SUBSTITUTE(D5," ","")*1

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

 Excel Convert Text with Spaces to Number

  • Hit ENTER to have the output.

  • AutoFill the rest cells

 Excel Convert Text with Spaces to Number

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.

 Excel Convert Text with Spaces to Number

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

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

 Excel Convert Text with Spaces to Number

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.

 Excel Convert Text with Spaces to Number

  • Press the ENTER button.

  • Now, AutoFill the remaining cells.

 Excel Convert Text with Spaces to Number

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


Similar Readings


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

 Excel Convert Text with Spaces to Number

  • 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.
=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.

 Excel Convert Text with Spaces to Number

  • Now, press ENTER to have the result.

  • Use Fill Handle to AutoFill the rests.

 Excel Convert Text with Spaces to Number


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

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo