How to Convert a Number to Text with the Green Triangle in Excel (6 Methods)

What Does the Green Triangle Indicator Mean?

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.

Method 1 – Adding an Apostrophe before Numbers

Type an Apostrophe (‘) in front of a number (e.g., ‘1234). This indicates that the number should be treated as text.

Press ENTER, and you’ll see a green triangle appear in the left-most corner of the cell. The green triangle indicates that the entry has been saved as text, and Excel detects it as an error.

Method 2 – Using the TEXT Function and Paste Special

The syntax of the TEXT function is:

=TEXT (value, format_text)

Where,

value; is 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.

Enter the following formula in a blank cell (e.g., E5).

=TEXT(D5,"0")

Here,

D5 is the reference to the numeric value you want to convert.
“0” specifies the desired format for displaying the value as text.

Press ENTER and drag the fill handle to apply the formula to the entire range.

After converting all entries to text, select the entire range, right-click, and choose Copy.

Right-click on the same range again, select Paste Special and click on Values. This triggers an error, and Excel displays green triangles in the cells.

Method 3 – Using Excel’s Text to Columns Feature

Select the cells containing the numbers you want to convert to text.
Go to the Data tab and choose Text to Columns from the Data Tools section.

Follow the Text to Columns Wizard:
- Keep the default selections in Step 1 of 3 and click Next.

In Step 2 of 3, again keep the default selections and click Next.

In Step 3 of 3, select Column data format as Text and click Finish.

- Text to Columns converts all number entries to text, displaying green triangles as an indication of error.

Method 4 – Using VBA Macro

Select the cells you want to convert to text.
Press ALT+F11 to open the Microsoft Visual Basic window.

Click Insert in the toolbar and select Module.

Paste the following macro into the module:

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.

Run the macro using the F5 key.
Return to the worksheet, and you’ll see that all number cells are converted to text, with Excel displaying green triangles as an indication of error.

Method 5 – Using Number Format in the Home Tab

Highlight the entire range.
Go to the Home tab and click the Number Format box icon (within the Number section).
Select Text from the options.