Hi readers!  Ever wondered why people use Excel for non-numerical data?  Me too.  That is why this post exists.  Organizations around the world use Excel for the capabilities that it includes for qualitative data, but I have always been unsure how this is supposed to work for entries that are not amounts.  Thankfully, the internet has loads of answers for us, so today we are going to explore the various text functions within Excel.

First up, a useful little tool called Join Strings which actually allows you to create a new column combining content from other columns.  The formula is simple; just add the ampersand (&) symbol between the content.  I actually came across this problem the other day at my internship site.  One of the partners at the firm came me this overwhelming list of information with information from those who subscribed to our blog posts (see Figure 1).

The partner just wanted a simple list of the contact names, but it would have taken quite some time to copy and paste each of the cells into a document.  Thankfully, this little tool came in handy.  See the example below (Figure 2) to see how I joined the two strings of text together to easily create this list for him.  My formula ended up being =B2&” “&C2 because I needed to add a space between the two values.  You can apparently add in as many qualifications as you would like.  It also helped to hide the rest of the information to make it more legible and less overwhelming.

Next up, I learned how to do the reverse of the above operation.  What if you want to extract characters from a string of text?  Easy, use the commands, LEFT, RIGHT, and MID to pull out anything you might want.  The formula just consists of the cell reference number and then the position and/or amount of characters that you will extract.  See Figures 3, 4, and 5 below to see how I extracted my first, last, and middle names from a single string of text using the LEFT, RIGHT, and MID tools, respectively.

Makes sense, right?  Most concepts in Excel actually do, contrary to popular belief.  Next up, use the LEN function to get the length of a string of text (see Figure 6).

Want to find the position of a certain text inside a string of text?  Just use the FIND function.  For example, I wanted to find out where “Lee” was in a string of text, so I entered =FIND(“Lee”, A1) into the formula bar (see Figure 7).  This might seem ridiculous now, but I actually could have used this concept when looking for positive feedback among a huge list of open-answer survey responses that Rainmaker received from one of its conferences this fall.

Perhaps more useful than that, however, is the SUBSTITUTE function.  This is similar to a “Find & Replace” command, but you can select the range of cells that you would like to analyze.  I used the previous example of a contact list to show how effective this can be.  As you can see in the “Final List,” there are some double spaces.  In the (CORRECTED) version, the double space was corrected with the SUBSTITUTE function.  This tool is incredibly useful in correcting mass amounts of data.

Last but not least, I had to teach myself how to change rows to columns to even begin to analyze this range of cells.  First, I selected the original group of cells as seen in Figure 9.

Then select a new section of a sheet in which to transpose or rotate the data from rows to columns or vice versa.  Select the button as seen below (Figure 10) that says “transpose.”

Here is the final product.  As you can see, the result is the opposite of what the original was in Figure 11.

Keep in mind Keyboard Shortcuts let you do things with your keys instead of using your mouse which increases your speed.  The most helpful shortcuts are COMMAND+C to copy, COMMAND+X to cut, and COMMAND+V to paste.

Thanks for reading, and feel free to use the attached Excel sheet to practice.

Qualitative Practice