Excel has a bunch of pretty nifty commands that can help marketers and analysts organize and manipulate large amounts of textual data. Why is this important? Well, business decisions are based on relevant analytic’s and reporting, and the building blocks of analytic’s are clean, up-to-date, complete data. Marketing campaigns are also built from clean data that will be used in presentations, mail merges letters, or targeted emails.
That means that the data used by any department must be accurate, clean, consistent, uniform, valid, and complete. First and last name fields must be entered and properly spelled, address should be correct; the states/province formats should be uniform; ditto for date formats and phone numbers; the list is endless. Most of the time, the data entry process is established and consistently adhered to, and analysts do not have to scrub the data every time they run a new report. However, sometimes a new list appears in their inbox, or they have to reformat a current list. This task can be nitty-gritty, but I have found that the following Excel functions have been particularly helpful in reducing the time to organize and reformat large amounts of textual data. Therefore, in no particular order, here are the top 5 excel functions and tools that are – in my opinion – severely underutilized and extremely helpful:
Concatenate. The word “concatenate” means “to combine” or “to join together”, which is exactly what this function allows you to do. Concatenate combines text from different cells into one cell. For example, if you have a first name column (column A) and a last name column (column B), concatenate can combine the text in columns A and B to create a full (combined) name in a new column.
Excel syntax: =CONCATENATE(text1, [text2], …)
How to use: Enter the function =CONCATENATE() in a target cell, and select/enter the cell address you want formatted within the brackets
Tips and Tricks: Remember, concatenate joins words together to make one word. It does not add spaces. If you need a space between the words, but you want all the words in one cell, you need to add a space into a new cell and then include the cell in the concatenate function.
Text-to-Columns. Text-to-Columns is a tool that can be used to separate data in a single cell into multiple cells. Going back to our concatenate example, if you want to separate names into first and last, use the Text-to-Columns button found under the Data menu. This is especially useful in separating addresses into streets, zip-codes, states or provinces (where applicable), and countries.
How to use. Select your cell or column, hit the Text-to-Columns button, and follow the directions. It does require some trial and error, so I recommend the Microsoft Office support page. Click here for a detailed description.
(Note: You can also use LEFT, MID, RIGHT, SEARCH, and LEN text functions to manipulate strings of text in your data.)
Paste Special. Oh the various joys of paste special! One could write an ode to it. Microsoft Word also has a paste special command, but it is not as robust as Excel’s – which is a huge pity. Paste special allows you to copy specific attributes from one part of an Excel spreadsheet and paste — or more specifically apply — onto the target data in another part of, or a new, Excel spreadsheet. These specific attributes could be the format or mathematical operation.
How to use. Select and copy the data that has the formatting you need, then select the data you want to update with those attributes, select paste options under the Home menu, choose paste special and follow the directions. The Microsoft Office support page has a very clear and detailed list of the different ways to paste. Click here to access the list.
Trim. The Trim function removes excess spaces in a text string except for the single spaces between words. It is especially useful in cleaning strings that have irregular spacing.
Excel syntax: =TRIM(text)
How to use: Enter the function =TRIM() in a target cell, and select/enter the cell address you want formatted within the brackets.
Proper. Proper capitalizes the first letter in a text string as well as that follow any character other than a letter. Converts all other letters to lowercase letters.
Excel syntax: PROPER(text)
How to use: Enter the function =PROPER() in a target cell, and select/enter the cell address you want formatted within the brackets.
I hope at least one of these Excel functions will be as helpful to as they have been to me. I know there are many, many, more functions that can be useful to marketers that I have not included. Which ones have you used? Please let us know in the comments.