Formula to convert text to numbers in Excel

Convert imported data from text format to numeric format

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

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

Notes worksheet

Excel’s default formatting for different types of data is often an indication that data has been imported or entered incorrectly. By default, numeric data and formula and function results are aligned to the right side of a cell, while 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 justified.

In addition, Excel indicates potential problems with a cell’s contents by displaying a small green triangle in the upper-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 data issues with the Paste Special feature.

To return this data in numeric format, use the VALUE function in Excel and Paste Special.

Paste Special is an advanced version of the Paste command that provides a range of options for what is transferred between cells during a copy/paste operation. These options include basic math operations such as addition and multiplication.

Multiply the values ​​by 1 with the special paste

The multiplication option in Paste Special multiplies all numbers by a specified amount and paste the result into the destination cell. It also converts text values ​​to numeric data when each entry is multiplied by a value of 1.

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

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

Prepare the worksheet

To convert text values ​​to numeric data, you must first enter some numbers as text. To do this, type an apostrophe ( ‘ ) in front of each number when entering it in a cell.

Open a new spreadsheet in Excel.

Select a cell D1 to make it the active cell.

In the cell, type an apostrophe followed by the number 23 (’23).

To press Come in! As shown in the image above, cell D1 has a green triangle in the upper-left corner of the cell, and the number 23 is aligned to the right. The apostrophe is not visible in the cell.

Select a cell D2.

In the cell, type an apostrophe followed by the number 45 (’45).

To press Come in!

Select a cell D3.

In the cell, type an apostrophe followed by the number 78 (’78).

To press Come in!

Select a cell E1

Enter the number 1 (no apostrophe) into the cell and press Enter.

The number 1 will be aligned to the right side of the cell as shown in the image above.

To display the apostrophe before the numbers in cells D1 through D3, select one of those cells, such as B.D3. The entry is in the formula bar above the worksheet ‘78 visible.