UPPER, LOWER & PROPER

UPPER, LOWER and PROPER are three functions that are very useful and help you save time and help ensure consistency.

These three functions all edit the particular case of one or more letters in a sentence (or to name it correctly a text string). They also allow you to change case sensitive data without having to retype the data again.

UPPER converts the whole sentence to be written as upper-case.

LOWER converts the whole sentence to be written as lower-case.

PROPER converts the text so that the first letter of each word is capitalised. Syntax

The formula is simple and depending on the type of text required the formula is written as follows:

=UPPER(TEXT)

=LOWER(TEXT)

=PROPER(TEXT)

The following screenshot shows how theses formulae can be used to style a particular sentence.

We often use these formulae to update lists that have grown over several months, for example the names and addresses of customers. Inevitably you will end up with inconsistencies such as STephen, Stephen, stephen, STEPHEN to give just a few examples.

Using =PROPER for names will ensure that the initial letter of the first and last name of contacts are capitalised.

Using =UPPER for county and post codes will ensure that all letters are in an upper case font (e.g. STAFFORDSHIRE and ST18 0RY).

This is a simple, quick and effective way to ensure that your data is consistent and most ERP, and other, systems will allow you to import data directly in to them in CSV or Excel format potentially saving you hours of time rekeying the underlying data.

Note of caution: Care must be taken where names are capitalised as the functions will not produce the desired results. As an example our company name, iFRC Ltd, would return Ifrc Ltd with =PROPER, ifrc ltd with =LOWER and IFRC LTD with =UPPER. However in large lists these types of names will usually be the exception rather than the rule.

We will continue to work with formatting issues in the next few blogs and look at TRIM, SUBSTITUTE and formatting dates in titles as well as nesting formula.