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”))