Handling plurals with the IF function

Singular or plural?

Sometimes when creating text you need to handle plurals correctly. The IF function makes it easy.

When creating flexible text you can use things like “there were 2 car(s)” which handles the singular and plural together.

If you need to be a bit more accurate then you can use the IF function.

In the image below there are formulas in columns B and C that determine whether to use the singular or the plural.

The formula in cell B2 is

="Car"&IF(A2<>1,"s","")

This formula starts with the singular word and then the IF function is used to determine if we need to add the letter s or not.

A2<>1 means A2 is not equal to 1. In general when creating an IF function you try to have the most likely situation as the TRUE argument. In this case it is most likely that the number won’t be 1, so we handle that first. If the number isn’t 1 we add an s. If the number is 1 then we add nothing. To add nothing you use “” which is a blank in Excel.

When the plural is not just adding an s on the end you need to use the part of the word that doesn’t change. in the case of cell C2 the formula is

="Compan"&IF(A2<>1,"ies","y")

This IF functions add the necessary ending text to get the correct singular and plural.

These formulas are a bit shorter than using

=IF(A2<>1,"Companies","Company")

This IF function includes the whole words, the result is the same but the formula is slightly longer. You would use this for words like goose and geese.

Related Posts

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.