But before we go there, did you know you can have Excel show your formulae instead of data? It’s great for showing how you’ve done something in Excel 😊 On the “Formulas” ribbon bar, click “Show Formulas”.

I get information with all sorts of strange caPitAlizaTioN –often the “I didn’t know I had my caps lock on when I hit caps lock to give you all upper case” lISA RUSHWORTH casing. Or the “I didn’t bother to hit shift so it’s all in lower case” lisa rushworth. And even the “I had caps lock on when I fixed something, but the rest is in lower case” lisa rusHWorth.

It’s not aesthetically pleasing, but some systems have case sensitive sorting – Sally is alphabetically before lisa. Some systems use case sensitive strings to collate – and “lisa rushworth”and “Lisa Rushworth” are two distinct records. And have I mentioned it’s not aesthetically pleasing?

But I’ve got an Excel formula that will create consistently capitalized data.

See:

In addition to using LOWER(), UPPER(), and PROPER(), you can combine these with LEFT() and RIGHT() to convert different parts of the string to different cases – as in my example of setting just the first letter capital.

Left(A14,1) takes the first character from the left of the value in A14. LEN(A14)-1 is the length of the value in A14 minus one.RIGHT(A14(LEN(A14)-1)) then takes one less than the entire string length from the right of A14 (i.e. everything except the first character).