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 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.

Dataset-Convert Number to Text Green Triangle in Excel

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


Download Excel Workbook


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.

error checking

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 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.

Overview


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

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

One of the common tricks to convert number to text displaying green triangle 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.

Apostrophe 1-Convert Number to Text Green Triangle in Excel

Step 2: Press ENTER, you see a Green Triangle appears 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.

Green triangle

➤ 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.

Converted

Read More: How to Convert Number to Text with Commas in Excel (3 Easy Methods)


Method 2: Using TEXT Function and Paste Special Feature to Convert Number 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. Coping converted entries then Paste Special them in Values results in error and Excel displays Green Triangle.

The syntax of the TEXT function is

=TEXT (value, format_text)

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).

=TEXT(D5,"0")

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

Text function-Convert Number to Text Green Triangle in Excel

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

Number to text

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.

Copy

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

Paste special

➤ 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.

Final Result

Read More: Excel VBA: Convert Number to Text with Format (A Total Guide) 


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).

Text to Column-Excel Convert Number to Text Green Triangle in Excel

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

Keep the default selections, Click on Next.

Step 1

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

Keep the default selections, Click on Next.

Step 2

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

Select Column data format as Text.

Click Finish.

Step 3

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

Convert number to text green triangle

Related Content: How to Convert Number to Text and Keep Trailing Zeros in Excel (4 Ways)


Similar Readings:


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 cells you want to convert to text. Then, Press ALT+F11 altogether to bring up Microsoft Visual Basic window.

vba-Excel Convert Number to Text Green Triangle In Excel

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

Module

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

Macro

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.

Final result

Read More: Excel VBA to Convert Number to Text (4 Examples)


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.

number section-Convert Number to Text Green Triangle in Excel

➤ 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.

Number to text conversion

Related Content: How to Convert Number to Text for VLOOKUP in Excel (2 Ways)


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

Excel Format Cells window allows users to change formats of any type. Though converting Number to Text using 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.

format cells-Convert Number to Text Green Triangle in Excel

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.

Format cells

➤ 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.

Number to text conversion

➤ 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.

Cross check

Related Content: Excel Convert Number to Text with Leading Zeros: 10 Effective Ways


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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo