Convert Text to Numbers With Excel Paste Special

01
of 04

Convert Imported Data From Text to Number Format

Convert Text to Numbers with Paste Special
Convert Text to Numbers with Paste Special. © Ted French

Sometimes, when values are imported or copied into an Excel worksheet the values end up as text rather than as number data.

This situation can cause problems if an attempt is made to sort the data or if the data is used in calculations involving some of Excel's built-in functions.

In the image above, for example, 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; however, 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 shows when data has been 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 three numbers - 23, 45, and 78 - in the image above are aligned on the left side of their cells because they are text values while the SUM function results in cell D4 is aligned on the right.

In addition, Excel will usually indicate 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 is indicating that the values in cells D1 to D3 have been entered as text.

Fixing Problem Data with Paste Special

Options for changing this data back to number format is to use the VALUE function in Excel and paste special.

Paste special is an expanded version of the paste command that gives you a number of options regarding exactly 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 will not only multiply all numbers by a certain amount and paste the answer into the destination cell, but it will also convert text values to number data when each entry is multiplied by a value of 1.

The example on the next page makes use of this feature of paste special with the results of the operation being:

  • the answer to the SUM function changing from zero to 146
  • the three values changing alignment from the left side of the cell to the right
  • the green triangle error indicators being removed from the cells containing the data
02
of 04

Paste Special Example: Converting Text to Numbers

Convert Text to Numbers with Paste Special
Convert Text to Numbers with Paste Special. © Ted French

In order to convert text values to number data, we first need to 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 that has all cells set to the General format
  2. Click on cell D1 to make it the active cell
  3. Type an apostrophe followed by the number 23 into the cell
  4. Press the Enter key on the keyboard
  5. As shown in the image above, cell D1 should have a green triangle in the top left corner of the cell and the number 23 should be aligned on the right side. The apostrophe is not visible in the cell
  6. Click on cell D2, if necessary
  7. Type an apostrophe followed by the number 45 into the cell
  8. Press the Enter key on the keyboard
  9. Click on cell D3
  10. Type an apostrophe followed by the number 78 into the cell
  11. Press the Enter key on the keyboard
  12. Click on cell E1
  13. Type the number 1 (no apostrophe) in the cell and press the Enter key on the keyboard
  14. The number 1 should be aligned on the right side of the cell, as shown in the image above

Note: To see the apostrophe in front of the numbers entered into D1 to D3, click on one of these cells, such as D3. In the formula bar above the worksheet, the entry '78 should be visible.

03
of 04

Paste Special Example: Converting Text to Numbers (Cont.)

Convert Text to Numbers with Paste Special
Convert Text to Numbers with Paste Special. © Ted French

Entering the SUM Function

  1. Click on cell D4
  2. Type = SUM(D1:D3)
  3. Press the Enter key on the keyboard
  4. The answer 0 should appear in cell D4, since the values in cells D1 to D3 have been entered as text

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

Converting Text to Numbers with Paste Special

  1. Click cell E1 to make it the active cell
  2. On the Home tab of the ribbon, click on the Copy icon
  3. The marching ants should appear around cell E1 indicating that the contents of this cell are being copied
  4. Highlight cells D1 to D3
  5. Click on the down arrow below the Paste icon on the Home tab of the ribbon to open the drop down menu
  6. In the menu, click Paste Special to open the Paste Special dialog box
  7. Under the Operation section of the dialog box, click on the radio button next to Multiply to activate this operation
  8. Click OK to close the dialog box and return to the worksheet
04
of 04

Paste Special Example: Converting Text to Numbers (Cont.)

Convert Text to Numbers with Paste Special
Convert Text to Numbers with Paste Special. © Ted French

Worksheet Results

As shown in the image above, the results of this operation in the worksheet should be:

  • the three values in cells D1 to D3 should now be aligned to the right in their cells
  • the green triangles should be gone from the top right corner of each cell
  • the results for the SUM function in cell D4 should now read 146 which is the total for the three numbers in cells D1 to D3