Let Excel’s Text Functions Help You “Save The Day”!

Have you ever been faced with the situation of having to import text from another source into Excel, and then to your dismay, discovered that the text did not look presentable? The text is either in all capitals or all lowercase letters–it’s not formatted in a professional manner.

Let’s say that you have imported data from a mainframe that included a large number of names of individuals, but the names appear in all lowercase letters. Your dilemma is you need to include the imported information as part of a larger report in Excel, and that the report must be formatted correctly and presented to your boss for an upcoming meeting. At this point, you are probably saying to yourself, “I will need to spend precious time re-typing so that it looks presentable, and I already have a million other things to do!” You might also be thinking to yourself, I know that I can use the Find and Replace feature, but I will have to be very careful so that no text is replaced in error.”

If you want your names to appear with the first letter of each name capitalized, simply use Excel’s PROPER function to help you.

In our example, we will use the names of individuals and their departments to illustrate how easy it is to use the PROPER function:

1.  Insert a blank column to the right of each column that you want to change. Then be sure to copy the appropriate column heading names at the top of the new columns:

2.  Type the PROPER function with the cell reference of the item to the left. In our example, we’ll use  =PROPER(A2)

3.  Copy the formula into the remaining cells where you want to see the names appear in the correct format.

4.  Repeat Steps 2 and 3 for the new Department column.

5.  When you have finished, simply right click on the Column A heading to get the shortcut menu and then select Hide Column. (IMPORTANT: Don’t delete the old column because you will lose your cell references and get error messages in your new column.) Then do the same for Column C. A screen shot of the final results appears below. There are a number of text functions that can help you. You can explore them by clicking on the Formulas tab and clicking the drop-down arrow for the Text icon in the Function Library Group.

For more information on BOSS software offerings, the BOSS degree and certificates, and how the BOSS program can help you with your career, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.