Singular / Plural text in Excel

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.


There are various options for making sure a text label matches the quantity.

There are various options for making sure a text label matches the quantity.

For example if the cell has 1 item you want the singular label:


1 cookie

But if the value is more than one, it’s nice if the label changes:


2 cookies


IF statement

Use this method when the text label is in a different cell to the value (in A2).

=IF(A2=1,”Cookie”,”Cookies”)

However this simple example doesn’t allow for zero cookies (horror!) . To handle that you need to nest IF statements:

=IF(A2=1,”Cookie”,IF(A2=0,”Nil, Nada”,”Cookies”))

First test for the value 1, if that doesn’t apply test for 0 and if both don’t apply then it’s 2 or more.

Excel IF function for singular plural image from Singular / Plural text in Excel at Office-Watch.com

To change the formatting of the cells according to value you need to use Conditional Formatting.

Update: Reader Bob J. suggests another path to a similar result:

I wonder if it is not grammatically correct to say “0 cookies” which makes the problem easier.

 

So to format a cell to put the cookie count in cell A2 in ‘english’ in another cell you could write

 

=text(A2,”0″)&” Cookie”&IF(A2<>1,”s”,””)

 

which produces:

 

0 cookies

1 cookie

2 cookies

etc.

 

The Text() function converts the number into a text string so it can be joined to other strings.

 

and to add the custom formatting consistent with the above use:

 

[=1]0 “Cookie”;[<>1]0 “Cookies”

Cell Format

A more powerful option is cell formatting either directly or as a cell style. The latter is recommended because it’s easier to manage and apply to multiple cells.

Cell formats tell Excel how to display the underlying cell value. There are many supplied formats for numbers, currency, date, time etc but there’s also the Custom option.

Excel custom cell forrnatting image from Singular / Plural text in Excel at Office-Watch.com

Custom is a powerful and somewhat confusing set of text strings that let you change the cell’s appearance according to value. Sadly, as you dig into this feature you’ll find some annoying limitations imposed by Microsoft, details below.

We’re only going to touch on a few of the features, but first here’s a simple example.

$#,##0;-$#,##0

This put a dollar sign in front of the value plus a comma separator if necessary. The # symbol indicates digit places to be used if necessary. After the semi-colon is the format for negative values – in this case it’s the same with a minus sign before the dollar sign.

You can add text into the cell that contains the value, rather than an adjoining cell that we used in the IF method above.

You can also have conditions in the formatting string. This feature isn’t obvious because it’s not shown in any of the Custom string examples supplied with Excel.

Here’s a simple example:

[=1]0 “Cookie”;[>1]#,##0 “Cookies”;

Excel custom cell for singular plural image from Singular / Plural text in Excel at Office-Watch.com

As you can see, cell A11 has a number value of 3 only but displays that value and a text label in the same cell.

The square brackets in the custom format contain the condition, each set of a condition and format is separated by a semi-colon.

The above example doesn’t allow for a zero value – if the cell equals 0, the cell is blank because there’s no matching formatting.

That’s why you should add a fallback format at the end after the last semi-colon.

[=1]0 “Cookie”;[>1]#,##0 “Cookies”;General

You might think this would be another improvement:

[=0]0 “NO Cookies!”;[=1]0 “Cookie”;[>1]#,##0 “Cookies”;General

Alas, Excel is limited to only two conditions for string format so the last formatting string won’t work.


A workaround

Office Watch Reader, Carolyn, came up with a partial workaround:

[=1]0 “Cookie”;[>1]#,##0 “Cookies”;”No Cookies”

“If the cell = 1 you get “1 cookie” if the cell is greater than 1 you to “x cookies” and if the cell is zero you get “No Cookies” ”

This gets around the two conditions limit by providing a third, assumed, option that deals with both zero values and negative values.

That’s OK if you can be sure there will be no negative cell values. If there is a negative number, it will misleadingly show “No cookies” and not the correct negative value.


Assumed Conditions

Happily there is a partial workaround for the two condition limit. The standard formatting string has some assumed conditions. We’ve already seen this in practice with this format $#,##0;-$#,##0 – where the semi-colon separates the positive and negative value formats without explicit [>0] and [<0] conditions.

In brief, the order of assumed conditions is:

Positive;Negative;Zero;Text

To make that work partly for cookies:

#,##0 “Cookies”;-#,##0 “Shortfall”;”NO Cookies!”;General

But that leaves us back with no singular text label for one cookie!

Microsoft giveth and Microsoft taketh away. The limit of two conditions badly needs lifting to at least 4 conditions (to accommodate Positive, Negative, Zero and Singular (1)) if not more.


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.