Recently I found an interesting way to handle plurals in Jordan Goldmeier’s book on dashboards.
When building a sentence or creating a heading you may need to handle plurals. An IF function is the usual solution. I handled plurals in this blog post.
Assuming A1 has a number in it and you want to include that number with a description you would write something like
="The order contains "&A1&" book"&IF(A1=1,".","s.")
The interesting technique that Jordan shared involved the LEFT function and a logic (boolean) calculation.
In Excel TRUE = 1 and FALSE =0.
When you use TRUE/FALSE in numeric calculations they are converted into their respective values. This is called coercing.
An alternative formula is
="The order contains "&A1&" book"&LEFT("s",A1<>1)&"."
The formula is slightly longer but has an interesting use of the LEFT function.
The symbols <> used together means “not equal to”.
If you use a 0 (zero) as the second argument in the LEFT function it returns a blank string.
Using a logical test to see if A1 is not equal to 1 as the second argument will return TRUE or 1 if the number is not 1 and FALSE or zero if the number is 1 (not a plural).
If the number in A1 is not 1 then TRUE is returned. The TRUE result is converted to 1 and the letter s is returned.
If the number in A1 is 1 then FALSE is returned. The FALSE is converted to 0 (zero) and a blank is returned.
This may not be a “better” formula than the IF function solution but it does highlight three things.
- The LEFT function can handle a zero as its second argument.
- Using a zero for the second argument returns a blank string, which is a common requirement for IF functions.
- TRUE and FALSE when converted to 1 and 0 can offer alternative solutions
I think the IF function solution is preferable because it is easier to understand.
If the plural is in the middle of the sentence then the LEFT solution may be slightly shorter than the IF function version as it doesn’t need to handle the full stop.
Its always good to examine different ways to handle a problem in Excel. It may not change the current solution, but it may open your mind to different ways to use formulas and functions for future solutions.
Rick Rothstein Excel MVP in the comments suggested yet another option.
="The order contains "&A1&" book”&TEXT(A1,"[=1]\.;\s\.")