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.

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:

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.

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

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

• Press ENTER.

• AutoFill the rest of the cells.

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.

• Check the Space box.
• Click Next.

• Check the Text box.
• Press Finish.

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

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

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

Here, the SUBSTITUTE function emits the space in cell D5. Then, the VALUE function converts them to numbers.

• Press ENTER.

• AutoFill the rest of the cells.

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

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:

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.

• Press ENTER.

• Use the Fill Handle to AutoFill the rests.

Read More: How Excel Formulas Convert Text to Number

Practice Section

You can practice here.

Download the Practice Workbook

Related Articles

• 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