Convert text to numbers with Excel Paste Special

When values ​​are imported or copied to an Excel worksheet, they may end up as text rather than numeric data. This situation causes problems if the data is sorted or used in calculations using built-in Excel 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.

  1. Convert imported data from text to number format
  2. Tips on the sheet
  3. Fix problematic data with Paste Special
  4. Multiply values ​​by 1 with Paste Special
  5. Create a worksheet
  6. Enter the sum function
  7. Convert text to numbers with Paste Special
  8. Worksheet Results

Convert imported data from text to number format

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

Instead of returning 146 as an answer, the function returns zero because the three values ​​were entered as text and not numeric data.

Tips on the sheet

Excel’s default formatting for various types of data is often a sign that data has been imported or entered incorrectly. By default, numeric data, as well as the results of formulas and functions, are aligned to the right of the cell and text values ​​are aligned to the left.

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

In addition, Excel indicates potential cell content issues 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 through D3 are formatted as text.

Fix problematic data with Paste Special

Use the VALUE function in Excel and Paste Special to convert this data back to number format.

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

Multiply values ​​by 1 with Paste Special

The multiplication option in Paste Special multiplies all numbers by a specified amount and pastes the answer into the target cell. It also converts text values ​​to numeric data when each item is multiplied by the value 1.

The example in the image below uses this special paste function. These are the results of the surgery:

  • The answer to the SUM function changes from zero to 146.

  • Three values ​​change the alignment of the left side of the cell to the right.

  • Error indicators with a green triangle are removed from cells that contain data.

Create a worksheet

To convert text values ​​to numeric data, first enter some numbers as text. You do this by entering an apostrophe () for each number when entered into a cell.

  1. Open a new sheet in Excel.

  2. Select cell D1 to make it active.

  3. Type an apostrophe followed by the number 23 ( ’23

  4. Click Input. As seen in the image above, cell D1 has a green triangle in the top left corner of the cell and the number 23 is right aligned. The apostrophe is not visible in the cell.

  5. Select cell D2

  6. Enter an apostrophe followed by the number 45 in the cell ( ’45

  7. Click Input.

  8. Select cell D3.

  9. Enter an apostrophe followed by the number 78 in the cell ( ’78

  10. Click Input.

  11. Select cell E1.

  12. Enter the number a (no apostrophe) in a cell and click Enter

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

To see an apostrophe before numbers entered in cells D1 through D3, select one of those cells, such as D3. The formula bar above the worksheet displays the entry: 78.

Enter the sum function

  1. Select cell D4

  2. Enter =SUM(D1:D3)

  3. Click Enter

  4. Reply 0 appears in cell D4 because the values ​​in cells D1-D3 were entered as text.

Apart from input, other methods of entering the SUM function in a worksheet cell include:

  • Keyboard Shortcuts

  • SUM Function Dialog Box

  • AutoSum

Convert text to numbers with Paste Special

  1. Select cell E1 to make it active.

  2. Select At home To copy .A dotted line appears around cell E1 to indicate that the contents of that cell are being copied to the clipboard.

  3. Highlight cells from D1 to D3.

  4. Click down arrow Insert to open the drop-down menu.

  5. Select Special insert to open the Paste Special dialog box.

  6. In the “Operation” section, select Multiply to activate this operation.

  7. Select Okay to close the dialog box and return to the worksheet.

Worksheet Results

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

  • The three values ​​in cells D1 through D3 are right aligned in each cell.

  • The green triangles have disappeared from the top right corner of each cell.

  • The result for the SUM function in cell D4 is 146, which is the sum of the three numbers in cells D1-D3.

Leave a Reply

Your email address will not be published.