Microsoft Office Tutorials and References

In Depth Information

=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,””)))/LEN(B1)

For example, if cell A1 contains the text
Blonde On Blonde
and B1 contains the text
Blonde,
the formula returns

2.

The comparison is case sensitive, so if B1 contains the text
blonde,
the formula returns
0.
The following for-

mula is a modified version that performs a case-insensitive comparison:

=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),””)))/LEN(B1)

Removing trailing minus signs

Some accounting systems use a trailing minus sign to indicate negative values. If you import such a report into

Excel, the values with trailing minus signs are interpreted as text.

The formula that follows checks for a trailing minus sign. If found, it removes the minus sign and returns a neg-

ative number. If cell A1 contains
198.43–
, the formula returns
–198.43
.

=IF(RIGHT(A1,1)=”–”,LEFT(A1,LEN(A1)–1)*–1,A1)

Expressing a number as an ordinal

You may need to express a value as an ordinal number: for example,
Today is the 21st day of the month.
In this

case, the number 21 converts to an ordinal number by appending the characters
st
to the number. Keep in mind

that the result of this formula is a string, not a value. Therefore, it can't be used in numerical formulas.

The characters appended to a number depend on the number. There is no clear pattern, making the construction

of a formula more difficult. Most numbers will use the
th
suffix. Exceptions occur for numbers that end with 1,

2, or 3
except
if the preceding number is a 1: hat is, numbers that end with 11, 12, or 13. These may seem like

fairly complex rules, but you can translate them into an Excel formula.

The formula that follows converts the number in cell A1 (assumed to be an integer) to an ordinal number:

=A1&IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),”th”,IF(OR(VALUE(RIGHT(A1))={1,2,3}),CHOOSE(RIGHT(A1),”st”,”nd”,”rd”),”th”))

This is a rather complicated formula, so it may help to examine its components. Basically, the formula works as

follows:

•
Rule #1:
If the last two digits of the number are 11, 12, or 13, use
th.

•
Rule #2:
If Rule #1 does not apply, check the last digit.

• If the last digit is 1, use
st.

• If the last digit is 2, use
nd.

• If the last digit is 3, use
rd.

•
Rule #3:
If neither Rule #1 nor Rule #2 apply, use
th.