Function Problems

How to Use Excel TEXT Function to Display Plus, Minus, and E for Zero

Amos Gikunda Oct 21, 2026 868 views

Question details

The user wants to format numbers in Excel so that positive values show a plus sign, negative values show a minus sign, and zeros are represented by the letter E.

How to Use Excel TEXT Function to Display Plus, Minus, and E for Zero

Product: Excel
Device & OS: not provided
Scenario: Applying custom number formatting to categorize and display specific symbols based on number values.
Observed behavior: Numbers need to be converted to text strings with defined custom formatting rules: + for positive, - for negative, and E for exactly zero.

Before you start

Ensure the data you are trying to format consists of numeric values. Keep in mind that using the TEXT function will convert your numbers into text strings, which means they cannot be used directly in further mathematical calculations without conversion.

Solution 1Recommended

Format Numbers using the TEXT Function

Apply a three-section custom format within the TEXT formula to specify the exact display behavior for positive, negative, and zero values.

The TEXT function uses format codes divided into sections separated by semicolons. The first section applies to positive numbers, the second to negative numbers, and the third to zeros.

Select the target cell

Click on an empty cell where you want the formatted text to appear.

Enter the TEXT formula

Type the formula =TEXT(A1, "+0;-0;E"), assuming cell A1 contains your original number.

Adjust for decimal places

If your numbers include decimals, modify the formula to include decimal places, such as =TEXT(A1, "+0.00;-0.00;E").

Apply to other cells

Press Enter to generate the result, then drag the fill handle down to apply this formula to other cells in your column.

Understanding Format Sections: The format string "+0;-0;E" explicitly tells the spreadsheet: if the value is greater than zero show '+value', if it is less than zero show '-value', and if it is exactly zero show 'E'.

Solution 2

Apply Custom Cell Formatting Directly

If you need to retain the numbers for future calculations, apply custom formatting directly to the cells instead of using the TEXT function.

Format Data Effortlessly

Easily Format Numbers and Use Functions in WPS Spreadsheet

WPS Spreadsheet fully supports Excel's TEXT function and custom formatting rules. You can quickly apply conditional formatting, utilize hundreds of built-in functions, and manage your data with a familiar, user-friendly interface.

1. Open your workbook: Launch WPS Office and open your spreadsheet document.
2. Enter your data: Type your numeric values into the desired column or row.
3. Apply the TEXT formula: In an adjacent cell, enter the formula =TEXT(A1, "+0;-0;E") and press Enter.
4. Drag to fill: Use the fill handle at the bottom right corner of the cell to drag and apply the formatting to the remaining data.

100% compatible with Microsoft Excel formulas and formattingAdvanced formatting tools for data visualizationLightweight, fast, and completely free to useBuilt-in templates and intuitive formula builder

microsoft office alternative - wps office

Frequently Asked Questions

Why did my numbers change to text after using the TEXT function?

The TEXT function is specifically designed to convert numeric values into text strings based on a specified format code. If you need to perform mathematical calculations on these values later, use Custom Cell Formatting (Ctrl+1) instead of the TEXT function.

How can I display 'Zero' instead of 'E'?

You can change the third section of the format code inside your formula. For example, using =TEXT(A1, "+0;-0;""Zero""") will output the word 'Zero' when the reference cell value is exactly 0.

Does the TEXT function work for dates?

Yes, the TEXT function is widely used to format dates and times. For instance, =TEXT(A1, "yyyy-mm-dd") will display a standard date format, converting the underlying serial number to a readable date text string.

What happens if a cell contains text instead of a number?

Custom formatting rules generally have a fourth optional section reserved for text formats (e.g., positive;negative;zero;text). In the TEXT function, if the reference cell contains text, it will typically return the text as-is unless an error occurs in the formula syntax.

Excel & Data Problems