Convert Text to Numbers With Excel Paste Special

Convert Text to Numbers with Excel Paste Special

 raw pixel \ Unsplash

When values are imported or copied into an Excel worksheet, the values can end up as text rather than as number data. This situation causes problems if the data is sorted or if the data is used in calculations involving Excel's built-in functions.

The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, and Excel for Mac 2011.

Convert Imported Data From Text to Number Format

Convert Text to Numbers with Paste Special

In the image above, the SUM function is set to add up the three values (23, 45, and 78) located in cells D1 to D3.

Instead of returning 146 as an answer, the function returns a zero because the three values have been entered as text rather than as number data.

Worksheet Clues

Excel's default formatting for different types of data is often one clue that data is imported or entered incorrectly. By default, number data, as well as formula and function results, are aligned on the right side of a cell, while text values are aligned on the left.

The numbers 23, 45, and 78 in the image above are aligned on the left side of the cells because they are text values. The SUM function result in cell D4 is aligned on the right.

In addition, Excel indicates potential problems with the contents of a cell by displaying a small green triangle in the top left corner of the cell. In this case, the green triangle indicates that the values in cells D1 to D3 are formatted as text.

Fix Problem Data with Paste Special

To change this data back to number format, use the VALUE function in Excel and Paste Special.

Paste Special is an expanded version of the paste command that provides a number of options regarding what gets transferred between cells during a copy/paste operation. These options include basic mathematical operations such as addition and multiplication.

Multiply Values by 1 with Paste Special

The multiplication option in Paste Special multiplies all numbers by a certain amount and pastes the answer into the destination cell. It also converts text values to number data when each entry is multiplied by a value of 1.

The example, shown in the image below, makes use of this Paste Special feature. These are the results of the operation:

  • The answer to the SUM function changes from zero to 146.
  • The three values change alignment from the left side of the cell to the right.
  • The green triangle error indicators are removed from the cells containing the data.

Prepare the Worksheet

Convert Text to Numbers with Paste Special

In order to convert text values to number data, first enter some numbers as text. This is done by typing an apostrophe ( ' ) in front of each number as it is entered into a cell.

  1. Open a new worksheet in Excel.
  2. Select cell D1 to make it the active cell.
  3. Type an apostrophe followed by the number 23 into the cell ('23).
  4. Press Enter. As shown in the image above, cell D1 has a green triangle in the top left corner of the cell and the number 23 is aligned on the right side. The apostrophe is not visible in the cell.
  5. Select cell D2.
  6. Type an apostrophe followed by the number 45 into the cell ('45).
  7. Press Enter.
  8. Select cell D3.
  9. Type an apostrophe followed by the number 78 into the cell ('78).
  10. Press Enter.
  11. Select cell E1.
  12. Type the number 1 (no apostrophe) into the cell and press Enter.

The number 1 is aligned on the right side of the cell, as shown in the image above.

To see the apostrophe in front of the numbers entered into cells D1 to D3, select one of these cells, such as D3. In the formula bar above the worksheet, the entry '78 is visible.

Enter the SUM Function

Convert Text to Numbers with Paste Special
  1. Select cell D4.
  2. Type = SUM(D1:D3).
  3. Press Enter.
  4. The answer 0 appears in cell D4 because the values in cells D1 to D3 were entered as text.

In addition to typing, other methods for entering the SUM function into a worksheet cell include:

Convert Text to Numbers with Paste Special

  1. Select cell E1 to make it the active cell.
  2. Select Home > Copy. A dotted line appears around cell E1 that indicates the contents of this cell are copied to the clipboard.
  3. Highlight cells D1 to D3.
  4. Select the Paste down arrow to open the drop-down menu.
  5. Select Paste Special to open the Paste Special dialog box.
  6. In the Operation section, select Multiply to activate this operation.
  7. Select OK to close the dialog box and return to the worksheet.

Worksheet Results

Convert Text to Numbers with Paste Special

As shown in the image above, here's how the results of this operation change the worksheet:

  • The three values in cells D1 to D3 are aligned to the right in each cell.
  • The green triangles are gone from the top right corner of each cell.
  • The result for the SUM function in cell D4 is 146, which is the total for the three numbers in cells D1 to D3.