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.
Search JabSto ::

Custom Search