Q: What's a green triangle?
A: A green triangle icon shown in the upper-left corner of a cell in Sheets indicates that the value in the cell is stored in text format.
Q: What's a number stored in the text format?
A: The text format here isn't identical to the plain text format in the toolbar. It means that the number has been converted to a character, like a Chinese character or a symbol, and has no properties of a number. Therefore, this number can't be used in formula-based calculations.
Number cell without a green triangle: The number in the cell still carries the properties of a number, is right-aligned by default, and can be used in formula-based calculations.
Number cell with a green triangle: The number in the cell has been converted to a character, is left-aligned by default, and a green triangle is displayed in the upper-left corner of the cell. If you use it in a formula-based calculation, the result may go wrong.
Q: How can I convert a cell with a green triangle into one without it?
A: Once you convert a cell with a green triangle into one without it, the number in the cell will have properties of a number and can be used in formula-based calculations.
Method: Hover your mouse over a cell with a green triangle and an exclamation mark in a circle button will appear on the left of the cell. Click the button and then the number in text format will be converted into a general number. If you want to batch convert numbers in multiple cells, select all the cells with a green triangle (the starting cell must be one with a green triangle), and click exclamation mark in a circle button - Convert to number.
Note: When you select multiple cells but the starting cell doesn't have a green triangle, the exclamation mark in a circlebutton won’t appear.
Q: How can I convert a cell without a green triangle into one with it?
A: When a number has many digits, Sheets would display the number in scientific notation. Therefore, Sheets isn't suitable for displaying long information such as ID card numbers. At this time, you can convert numbers to text to display them in full.
Method: You can use the following shortcuts to convert numbers to text. Once converted, numbers will be changed to text, will be automatically left-aligned, and will lose their properties of a number.
Mac | ⌘ + Shift + S |
Windows | Ctrl + Shift + S |
Q: Why can’t I find data in a cell with a green triangle using the VLOOKUP function?
A: Although the numbers in general cells and in cells with a green triangle look the same, the former is actually a digit, while the latter is text. That's why you can't find matches using the VLOOKUP function.
You can unify content properties of cells in two ways:
- 1.The number of digits in a cell with a green triangle is smaller: Format cells as numbers, that is, without a green triangle in the upper-left corner, and then you can find results with the VLOOKUP function.
- 2.The number of digits in a cell with a green triangle is greater: If you convert the value to a number, the precision may be compromised. For example, if you convert the value 12345678987654321 to a number, the value may change to 12345678987654300. We recommend you convert all values to text (with a green triangle) to ensure data precision and validity of the VLOOKUP function.
👍 If article has been helpful, please give us a thumbs-up.