Excel Tip – Text Functions #1

There are times text needs to be converted to lower case, proper case, or upper case for consistency sake. Excel provides functions to easily convert text in these cases.

However, Excel does not convert text to sentence case — the first letter only is capitalized. Using a combination of functions will easily convert text to sentence case.

The formula to convert the text in A1 is:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

For examples, see the following:

Examples of text functions.
Examples of Text functions

Next week will continue with more useful text functions.

Vlookup — True or False?

Vlookup is one of the functions I use the most. It can locate data within a table and return a value. One of the most confusing things about the function is whether or not to include the True or False at the end.

When using the Excel Function wizard, it argument in the function is called Range_lookup.

Function Wizard for Vlookup.
Vlookup in Function Wizard

Simply stated, use a TRUE or 1 when you want an approximate match. Use a FALSE or 0 when you need an exact match.

Use TRUE when the data in the lookup table does not contain all of the possible data points. For example, in the figure below, if you want to look up the tax rate you would use an approximate match since all incomes are not in the table. If the net income were 100,001, it would return a rate of 39%.

Tax Table

It finds the closest value that is not larger than the lookup value.

In order to use an approximate match, your data must be sorted as well. Otherwise, use a FALSE or exact match.

For more examples, see this YouTube video:

Video explanation