Microsoft Office Tutorials and References
In Depth Information
Advanced Text Formulas
occurrences of a particular substring (contained in cell B1) within a string (contained in cell A1).
The substring can consist of any number of characters.
=(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 formula 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 negative 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 (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”))
 
Search JabSto ::




Custom Search