Convert Text formatted number in number value

Process Flow: Convert Text formatted number in number value 

Method 1: Use Shortcut Keys

  1. Select Range:

    • Highlight the cells containing numbers stored as text.

  2. Shortcut Key Sequence:

    • Type the shortcut Alt + D + E + F.


  1. Result:

    • The text is converted to numeric values.


Method 2: Use Paste Special and Multiply

  1. Preparation:

    • Select a blank cell that does not have this issue.

    • Type the number 1 into the blank cell and press Enter.

  2. Copy the Value:

    • Press Ctrl + C to copy the cell containing the number 1.

  3. Select the Range:

    • Highlight the cells that have numbers stored as text.

  4. Apply Paste Special:

    • Go to the Home tab.

    • Click Paste > Paste Special.

    • In the Paste Special dialog box, select Multiply, then click OK

    • Result: Excel multiplies each cell by 1, effectively converting the text to numbers.

Method 3: Use Value Number Function

  1. Add a New Column:

    • Insert a new column adjacent to the data range.

  2. Apply Formula:

    • In the first cell of the new column, type the formula =VALUE(A1) (replace A1 with the appropriate cell reference).

  3. Extend the Formula:

    • Drag the formula down to apply it to the entire range.

  4. Optional Cleanup:

    • Copy the converted values and paste them as values in the original column.

Method 4: Use Paste Special with Ctrl + Alt + V

  1. Preparation:

    • Type 1 in any unformatted cell within the Excel sheet.

    • Copy the cell containing 1.

  2. Select the Range:

    • Highlight the range where text needs to be converted.

  3. Apply Paste Special:

    • Press Ctrl + Alt + V.

    • In the Paste Special dialog, select Values and Multiply, then press Enter.


  4. Result:

    • Text values are converted to numeric values.

Tips

  • Always double-check your data after conversion to ensure accuracy.

  • Save a backup of your Excel file before performing bulk transformations.