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

Below is a dataset with personal information in the Name, City, and Phone columns.

Excel Convert Text with Spaces to Number

Method 1 – Using the SUBSTITUTE Function to Convert Text with Spaces to Number

1.1. Using SUBSTITUTE Function with Summation Rule

STEPS:

Create a new column to have the output. I have created a column named Converted Output to have the result.
Input the following formula:

=SUBSTITUTE(D5," ","")+0

Here, the formula emits the space in cell D5’s value. The addition is because SUBSTITUTE converts any value to text, and adding the value 0 will preserve the number and the format to numeric.

Press ENTER.

Excel Convert Text with Spaces to Number

Use the Fill Handle to AutoFill the rests.

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

1.2. Using SUBSTITUTE Function with Multiplication 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:

=SUBSTITUTE(D5," ","")*1

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

Excel Convert Text with Spaces to Number

Press ENTER.

AutoFill the rest of the cells.

Excel Convert Text with Spaces to Number

Method 2 – Applying Text to Columns and CONCAT & 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.

Check the Delimited – Characters such as commas or tabs separate each field box.
Press Next.

Excel Convert Text with Spaces to Number

Check the Space box.
Click Next.

Check the Text box.
Press Finish.

Excel Convert Text with Spaces to Number

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

Enter 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 numbers.

Excel Convert Text with Spaces to Number

Press ENTER.

AutoFill the remaining cells.

Method 3 – Using the VALUE – SUBSTITUTE Formula to Convert Text with Spaces to Number

STEPS:

Create a new column (i.e., Converted Output) to have the output.
Enter 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 numbers.

Press ENTER.

Excel Convert Text with Spaces to Number

AutoFill the rest of the cells.

Method 4 – Combining VALUE, SUBSTITUTE and CHAR Functions

STEPS:

Create a new column (i.e., Converted Output) to have the output.
Enter 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 numbers.

Excel Convert Text with Spaces to Number