How to Convert Number to Text with Green Triangle in Excel

Excel has an inbuilt data display orientation to indicate entries such as Numbers or Text. On occasions, we need to convert number to text with green triangle in Excel. These occasions may refer to printing document issues, making entries inoperable using formulas, etc. We can convert number to text using multiple Excel features, TEXT function as well as VBA macro.

Let’s say, we have Employee data and their Monthly Gross Salary amounts in a dataset. We want to convert the salary amounts to text displaying the green triangle as an indication of the text value present in cells.

 In this article, we demonstrate multiple ways to convert number to text with green triangle in Excel.

What Does Green Triangle Indication Mean?

Excel offers error-checking indications in selected colors. We can find and change the color option Excel worksheet File ribbon > Options > Formulas.

In case Excel detects an error in its calculation it indicates the error by displaying a triangle on the left-most side of the cell in a preset color (by default it’s Green). As a result, when we convert a number to text Excel detects it as an error and indicates it with a green triangle inside the cells.

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

Method 1: Adding an Apostrophe before Numbers to Convert Number to Text with Green Triangle 

One of the common tricks to convert numbers to text displaying green triangles is to insert an Apostrophe (‘) before it. However, it’s a manual process and we have to type an apostrophe before each number or entry to convert it to text.

Step 1: Type an Apostrophe (‘) in front of a number as shown in the following image.

Step 2: Press ENTER, and you see a Green Triangle appear on the left-most corner of the cell. The Green Triangle indicates the entry has been saved as text and Excel detects it as an error.

➤ Insert an apostrophe in front of each entry, and you’ll find the Green Triangle appearing in all cells as depicted in the below picture.

Read More: Convert Number to Text in Excel with Apostrophe

Method 2: Use TEXT Function and Paste Special Feature to Convert Numbers to Text in Excel

The TEXT function converts numeric values to text strings. However, converting numbers to text doesn’t instigate Excel to show Green Triangles. In order to display Green Triangle, there has to be an error present. Copying converted entries then Paste Special them in Values results in error and Excel displays Green Triangle.

The syntax of the TEXT function is

value; the number you want to convert.

format_text; specific format to display the value in.

The TEXT function stores the value as text. But the outcome triggers no error that’s why Excel shows no Green Triangle in resultant entries.

Step 1: Type the latter formula in any blank cell (i.e., E5).

In the formula, D5 is the value reference and “0” is the format_text. We want the numbers as text in “0” format.

Step 2: Use the ENTER key to apply the formula, then, Drag the Fill Handle to insert the formula in the entire range.

Step 3: After converting all the entries to texts, Select the entire range and Right Click on it. The Context Menu appears. From the Context Menu options, Select Copy.

Step 4: Again, Select the same range then right-click on It. Choose the Paste Options Value from the Context Menu options.

➤ Pasting TEXT function converted entries as Paste Special Values triggers an error. For that reason, Excel displays Green Triangles in cells as shown in the below picture.

Read More: How to Convert Number to Text without Scientific Notation in Excel

Method 3: Converting Number to Text with Green Triangle Using Excel’s Text to Columns Feature

Another Excel feature named Text to Columns converts numbers to texts resulting in Excel displaying Green Triangles as an indication of error. The Text to Columns feature is in the Data tab.

Step 1: Firstly, Select the cells you want to convert numbers into texts. Go to the Data tab > Select Text to Columns (from the Data Tools section).

Step 2: Convert Text to Columns Wizard Step 1 of 3 appears.

Keep the default selections, Click on Next.

Step 3: The 2^nd Convert Text to Columns Wizard appears.

Keep the default selections, Click on Next.

Step 4: Lastly Convert Text to Columns Wizard Step 3 of 3 pops up.

Select Column data format as Text.

Click Finish.

➤ Text to Columns converts all the number entries to texts displaying Green Triangles in each cell.

Read More: Convert Number to Text with 2 Decimal Places in Excel

Method 4: Using VBA Macro to Convert Number to Text with Green Triangle in Excel

VBA macros are strong tools to convert numbers into texts. A couple of lines of macro converts numbers to texts. Normally the VBA CStr function converts values to strings.

Step 1: Select the number of cells you want to convert to text. Then, Press ALT+F11 altogether to bring up Microsoft Visual Basic window.

Step 2: In Microsoft Visual Basic Window, Click on Insert (from the Toolbar) and afterward select Module (from the options).

Step 3: Paste the following macro in the Module and use the F5 key to run the macro.

Sub Convert_Num_to_Text()
Dim Cells As Double
For Each cell In Selection
If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then
Cells = cell.Value
cell.NumberFormat = "@"
cell.ClearContents
cell.Value = CStr(Cells)
End If
Next cell
End Sub

The macro takes cell entries from the selection and declares them as Double. Then, the VBA FOR loop checks whether each cell is not empty and numeric or not. Assigning cells and their format, the macro clears all the cell contents. Afterward, the VBA CStr function converts cell values to strings.

➤ After running the macro, return to the worksheet. You see all the number cells are converted to texts. And by program Excel displays Green Triangles in cells as an indication of error.

Method 5: Using Number Section Option in Home Tab

Excel offers a Number Format box in the Home tab within the Number section. In that Number Format box, Excel by default displays the Number types of selected cells. In that box, there are multiple options to convert selected cells into. But Excel doesn’t display the Green Triangle as it doesn’t consider text conversion using Number Format as an error.

Steps: Highlight the entire range then go to Home > Click on Number Format box icon (in the Number section) > Select Text.

➤ Selecting Text from the Number Format options converts numeric values to text. And you can cross-check the conversion using the Number Format display as depicted in the following image.

Method 6: Using the Format Cells Window to Convert Number to Text

The Excel Format Cells window allows users to change formats of any type. Though converting Number to Text using the Format Cells window doesn’t trigger the Green Triangle to appear.

Step 1: Select the entire range, then, Click on the Font Setting icon in the Home tab.

Step 2: The Format Cells window appears. In the Format Cells window, Select the Number section and Choose Text (from the Category options).

Click on OK.

➤ All the number values get converted to text. Excel doesn’t display any error tracking as it doesn’t consider text conversion using Format Cells as an error.

➤ To check the value type of the cells, you simply select them, and the Number section (in the Home tab) display box shows them as Text.

Download Excel Workbook

Conclusion

In this article, we demonstrate multiple ways to convert number to text green triangle in Excel. Also, we clarify the underlying reasons for the green triangle appearing in cells. Hope this article resolves issues regarding the number-to-text conversion and green triangle. Comment if you have any further inquiries or have anything to add.

Related Articles

• How to Convert Number to Text with Leading Zeros in Excel
• How to Convert Number to Text with Commas in Excel
• How to Convert Number to Text for VLOOKUP in Excel
  
• How to Convert Number to Text and Keep Trailing Zeros in Excel

<< Go Back to Excel Convert Number to Text | Learn Excel