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.

Excel Convert Text with Spaces to Number


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.

 Excel Convert Text with Spaces to Number

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

 Excel Convert Text with Spaces to Number

  • Hit ENTER to have the output.

  • AutoFill the rest cells

 Excel Convert Text with Spaces to Number


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.

 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


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.

 Excel Convert Text with Spaces to Number

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

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


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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo