How to Convert Green Triangle to Number in Excel (6 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

A green triangle will appear in the top-left corner of the cell when Excel detects an error in any cell. In the case of a number, this appears when that number is formatted as text. In this article, we will show you six quick methods to convert the green triangle to a number in Excel.


How to Convert Green Triangle to Number in Excel: 6 Ways

There will be six easy approaches to convert a green triangle to a number in Excel. Firstly, we will use the Convert to Number feature. Secondly, we will utilize the Paste Special option. Thirdly, we will use the Text to Column feature. Following that, we will apply the VALUE function. After that, we will use a mathematical operator. Lastly, we will employ Power Query to achieve the objective of this article. Additionally, you can see a quick view of the output of this article from the image below.

6 Handy Approaches to Convert Green Triangle to Number in Excel


1. Using Convert to Number Option

When a cell with a green triangle is clicked, an error message is shown. In the case of the number formatted as text, this message will be shown upon hovering over the error sign: “the number in this cell is formatted as text or preceded by an apostrophe.” We can click on that error sign and select the Convert to Number option to fulfill the objective of this article.

Steps:

  • Firstly, copy the values from the range B5:B10 to the range C5:C10. We are doing this for better visualization, you can replace the values.

Using Convert to Number Option to Green Triangle to Number in Excel

  • After that, select the entire column values.
  • Then, a yellow warning sign will pop up, and click on that.
  • Finally, select the Convert to Number option from that list. The following animated image shows you the process.

Animated Image of Convert Green Triangle to Number in Excel

Read More: Excel Convert to Number Entire Column


2. Utilizing Paste Special Feature to Convert Green Triangle to Number

In order to turn the green triangle into a number, we will make use of the Paste Special feature. We will type 1 or 0 anywhere in the sheet, then using a keyboard shortcut, we will bring up the Paste Special dialog box. However, a copied value from an empty cell will work just fine to use this feature. Finally, we will change two settings to achieve the goal of this article.

Steps:

  • To begin with, copy 1 which is in cell B12.
  • Then, select the cell range C5:10 and press Ctrl+Alt+V.

Utilizing Paste Special Feature to Convert Green Triangle to Number

  • So, the Paste Special dialog box will appear.
  • Then, select Values and Multiply from the Paste and the Operation sections respectively.
  • Lastly, press OK.

  • Thus, this will convert the green triangle to a number.

Read More: How to Convert Text with Spaces to Number in Excel


3. Using Text to Column Wizard

In this third method, we will use the Text to Column wizard. This feature is useful for converting the green triangle to a number in Excel. Remember, this wizard is available only in Excel 2013 and later.

Steps:

  • Firstly, select the cell range C5:C10.
  • Secondly, from the Data tab, select Text to Columns.

Using Text to Column Wizard to Convert Green Triangle to Number in Excel

  • Therefore, a dialog box will appear.
  • We do not need to change anything and just press Finish.

  • In conclusion, this will be the completion of third method to convert a green triangle to a number in Excel.


Similar Readings


4. Applying VALUE Function to Convert Green Triangle to Number

In this section, we will apply the VALUE function to convert a green triangle to a number. Then, we will use the Fill Handle to apply that formula to the rest of the cells. This will AutoFill the formula in Excel to convert all the green triangles to number format.

Steps:

  • To begin with, type the following formula in cell C5.

=VALUE(B5)

Applying VALUE Function to Convert Green Triangle to Number

  • Then, press Enter, and using the Fill Handle, insert the formula into the rest of the cells.


5. Using Mathematical Operator

We will use a mathematical operator in this section to convert a green triangle to a number. There are mainly four mathematical operators: addition, subtraction, multiplication, and division. If we use this with a number (formatted as text), then that pseudo text (number with a green triangle) will be converted into a number.

Steps:

  • Firstly, select the cell range C5:C10.
  • Secondly, use the following formula.

=B5+0

Using Mathematical Operator to Convert Green Triangle to Number in Excel

  • After that, press Ctrl+Enter. This will fill the formula to the selected cells.

Read More: How to Convert Alphabet to Number in Excel


6. Employing Power Query

In this last method, we will employ the Power Query feature of Excel to convert a green triangle to a number in Excel. Before bringing up the Power Query window, we will need to convert the range to a table.

Steps:

  • First of all, select the cell range B4:B10.
  • Second of all, press Ctrl+T.
  • Hence, this will bring up the Create Table dialog box.
  • Third of all, press OK.

Employing Power Query

  • So, it will transform the selected range into a table.
  • Afterward, again select that cell range and from the Data tab, select From Table/Range.

  • Therefore, the Power Query Editor window will appear.
  • After that, we can see that the data type is changed automatically.
  • Then, from the Close & Load option, select “Close & Load To…”.

  • So, the Import Data dialog box will appear.
  • Then, select cell B12 as the output under the Existing worksheet option.
  • Finally, press OK.

  • This will convert the green triangle to a number and the output will look like this.

Read More: How to Convert Text to Number with Excel VBA


Download Practice Workbook

You can download the Excel file from the link below.


Conclusion

We have shown you six easy methods to convert the green triangle to the number in Excel. If you face any problems regarding these methods, feel free to comment below. Moreover, you can also leave any feedback for us, so we can serve you better.

However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Thanks for reading. Keep excelling!


Related Articles

Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo