Hello Excellers. I love Friday Formula time. Don’t forget to #followfriday on Twitter……
This is a really useful formula, and one that a lot of Excel users dont use that often, in fact, TEXT functions I think are under used big time when they can really save time and increase efficiency. One of those is the UPPER function. This converts text to UPPERCASE.
Handy… so even if you have an Excel spreadsheet that is a mix of upper and lower cases (yes even within the same word- we have all inheritied those types of sloppy data sets) then this little beauty will tidy it up for you. No need to manually re type all of the data.
The syntax of the formula is
=UPPER(text)
Where
text is required in the formula. This is the text you want converted to uppercase. Text can be a reference or text string.
So let’s take a look at it in action. Here is is a small extract of a terrible set of data. We want to set all of the text to uppercase, ready to import into a database where the expected format is..uppercase. We have a mix of upper and lower cases.
First all we need to use helper columns as we are going to reference our old data in a new area of our worksheet. So I have created a new set of columns ready for my new data, this is only a small set of data, if it was larger you could always set up you new or helper columns on another worksheet in your workbook.
So, here are my helper columns ready, in columns F,G and H
So the formula is really simple, in G2 type =UPPER(C2)then hit return (I have als0 just kept the data set complete by referencing the Customer Ref by using the formula =B2 in F2 and dragged it down Column F). Then drag your formula across all of your cells to complete the data transformation.
The last part if to select the whole data set, put the cursor in the data set and hit CTRL+A once. This the fastest way to do this and then hit Paste Special -Values to have your new clean an tidy data set ready in a few clicks.
Yay clean data. Do you use this function a lot?. Or do I just always seem to have the worst data given to me??????
- Create A Micro Chart In Excel
- Hide all of your Excel formulas from prying eyes!
- Speed Up Your VLOOKUPS!
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
