Text in Excel formulas

Excel is one of the best tools around for working with numerical data sets, but it is also a valuable tool to utilize when working with sets of textual data.

By Kara Monroe

Excel is one of the best tools around for working with numerical data sets, but it is also a valuable tool to utilize when working with sets of textual data. In this article we’ll explore some of the functions Excel has for working with text and a variety of ways in which these can be used together in order to get the data you want as efficiently as possible.

PUTTING STRINGS TOGETHER

Excel uses a function called CONCATENATE to allow you to combine up to 30 text strings together. If you need to combine more than 30 strings or if you simply can’t remember how to spell CONCATENATE, you can use the ampersand key (&) instead. For example, say you have a spreadsheet containing first names in one column and second names in another. Type your first name in cell A1 and your last name in cell B1. To combine the two names into a single cell and a single word, you would use the formula = CONCATENATE(A1, B1) or =A1&B1.

Notice that it puts the strings together without the space. That’s no problem. You can add in a space using a set of quote marks with a blank space between them. For example, replace the formula =A1&B1 with =A1&” “&B1. You can put anything between the quote marks – not just a blank space. For example, consider the following set of U.S. States and Capitals

A B

1 Indianapolis Indiana

2 Columbus Ohio

3 Tallahassee Florida

4 Augusta Maine

You can create a sentence with each of these utilizing the CONCATENATE or &. Your formula could read =A1&” is the capital of “&B1. Note that there is a space after the first quote mark and before the second one so that the spacing is appropriately maintained.

TAKING STRINGS APART

Sometimes when dealing with text, less is more. To extract certain parts of a word you can use the LEFT, RIGHT, and MID functions. Each of these functions does exactly what they way they do – take characters from the left, right, and middle of a string. LEFT and RIGHT require the same syntax. To get the left side of a string, you use the function =LEFT(text, number_of_characters). For example, consider our set of states and capitals from the previous example. You could use =LEFT(A1,4) to return the result Indi. The formula =RIGHT(B4,3) would return ine (from the end of the word Maine). LEFT starts at the left-hand side of the selected word and returns the number of characters you indicate. Right does the same thing, although it starts from the right-hand side of the word. The MID function goes one step further – MID allows you to start anywhere in the word and return a set number of characters. The MID formula looks like =MID(text, starting_character, number_of_cha racters). Continuing with our states and capitals, the formula =MID(B3,3,6) would give the result chigan.

TAKING STRINGS APART AND MAKING NEW STRINGS You can use the LEFT, RIGHT, and MID functions together with the CONCATENATE function to create new words. For example, let’s combine the following together:

=(LEFT(A1,4))&(MID(B3,3,6)). You should now have the combined state of Indichigan.

MAKING THINGS PROPER

Datasets often come in mixed case, all uppercase, or all lowercase…and typically never in the case you want the data to be in its final format. No worry – Excel has a function to help with any of these situations. The most useful, in my opinion, is the PROPER function. The PROPER function capitalizes the first letter of the string and turns the rest of the characters in the string to lower case. Let’s try it. Enter the following in cells A1, A2, and A3

INDIANA

ohio

MiCHIgan

Now, in a blank cell, enter =PROPER(A1). You should now see Indiana in that cell. Repeat for cells A2 and A3. Notice that the PROPER function doesn’t even mind the crazy mixed up case of the world MiCHIgan from our example.

Excel also has functions to make all letters uppercase (the UPPER function) and all letters lowercase (the LOWER function). These each work the same as the PROPER function does. Simply enter = UPPER(A2) and you should get OHIO or =LOWER(A3) and you should get michigan.