Excel Formula Help - PROPER function for Managing Text calculations
Microsoft Office Excel is a powerful tool for manipulating and organising both numeric and text data. Let's take a look at a few calculations the can be used to manage text, beginning with lists of names:
In this spreadsheet, we find first names and second names in different columns. We would like to place a single full name, comprising of first and second names, neatly in a single column. Of course, we could do this one by one but that would be unreasonable with a long list. Instead, let's access some great Excel tools to make the job easier, quicker and more accurate.
To join names from separate columns together, we simply use the '&' character, known as the concatenation operator: The formula =A2&B2 is entered in cell C2:
OK, so now we have both names on a single column, but it doesn't look great; in the real world, no one writes the name JAMESALLEN. Provide spacing between first and last names by editing the formula using double quotations: =A2&'' ''&B2.
Then, use the mouse to select the whole of column C down to the end of the names, and hit Ctrl-D to copy the formula to all the other names.
Almost done. To remove capitalisation, we can use the PROPER function that capitalises initial letters in a text string (as well as any other letter that follows non-letter characters), and converts all other letters to lower case; so, you now have =PROPER(A2&" "&B2) in C2:
Again, select the list of full names in column C (including C2 which contains the updated formula) and then Ctrl-D again to amend.
Other options are also available for converting text, for example, between upper and lower case with the functions =UPPER() and =LOWER().
Now that's done, you may be thinking of deleting columns A and B containing the first and last names. If you do, you will find that all the contents of column C disappear as their formulas are dependant on the data you have just removed.
A simple way to fix this is to select the contents of column C that you wish to keep, right click, 'Copy' and then on the drop down select 'Paste Special' and 'Values (V)', the left-most clip-board icon under Past Values, see diagram below:
You are now free to delete your unwanted data in columns A and B.
To learn more about the =PROPER function, click here.
