The problems in Microsoft Excel that the average person comes across tend to be similar from one person to the next. While working on a recent project with a client, it occurred to me that there are many text functions that everyone might like to know more about.
To start, all Excel functions have the ability to calculate or manipulate your data. Many people think of Excel as a resource to build financial spreadsheets and calculations, but the program can do much more than that.
When working with text data, common problems arise such as capitalization issues, additional spaces, removing portions of unwanted data, and combining multiple pieces of data into one to name a few. Focusing on capitalization to start, Excel has three text functions to help you.
The ‘Upper’ function will turn any source data into all upper-case, the ‘Lower’ function will convert all your source data to lower-case, and the ‘Proper’ function takes any text and forces the first letter to be capitalized with the remaining letters in lower-case. For any of these text functions, you can reference another cell with the source data and display the result in separate cell with the desired case.
Another example of text manipulation often arises when you are given data from a third-party system. Let’s say that you have an HR system that generates a CSV file with names and phone numbers. In order to create a list of emails for your company you might want to join the first and last name to create an email address.
Using the ‘Concatenate’ function, you can reference several pieces of data and combine them into one. You might use Concatenate to have the first name, an underscore, the last name, and then your company URL (e.g. @example.com), and generate an email for every employee. In this example, you can let Excel save you a ton of time letting the Excel text functions manipulate your data versus entering the email address manually.
There are several other useful Excel text functions that you may find helpful in your daily work. However, it can be intimidating to try and learn them all. I would advise any beginner to Excel functions to use the ‘Insert Function’ command where you can learn about these functions in more detail. The ‘Insert Function’ command provides a definition of the function, and a clear definition of any corresponding arguments that the function needs in order to work properly. I recommend this technique to all of my students as a great way to find and understand the right function for you.
Don’t be afraid to jump in and try new text or manipulation functions. As always, make a backup copy of your data first, just in case something you do cannot be undone. Most importantly, have fun with Excel and find new ways to let the program do all the heavy lifting with your data.
[Originally posted in the Coloradoan on 1/16/2014]