Working with Text Data on Microsoft Excel — study guide

Honors Statistics

Working with Data with Microsoft Excel

Text and Numerical Data

Text data analysis is an important component of general data analysis.  Text data can be made up of alphanumeric characters which are treated like characters.  Examples of numeric data that are not arithmetic include phone numbers, house numbers, zip codes and course numbers.  These data can be converted to numeric data if desired for sorting purposes.

Furthermore, parts of a “text string” can be pulled out of sequence for analysis purposes, or alternatively text strings can be combined.  Research the following functions in Excel to see how they are used, and then do the exercise.

Even though there is a very robust Help utility in Excel, one has to be familiar with the language and shorthand used, or the instructions can be unintelligible.  Likewise, it is important to know the name of the function you would like to use, or else it can be very time consuming trying to get something done.

Some key ideas and definitions:

The term “text string” is used to designate alpha characters strung together.  Alpha characters can be numeric as well as alphabetic, depending how they are formatted.  Remember that cell formatting allows you to designate cells as text, numbers (of different types), date (in different styles) etc.

Examples of numeric data that you might want to treat as a text would be zip codes and phone numbers.  As a number, the zip code 09999 would not print the leading 0.  In order to get Excel (and other databases too) to print the leading zero, the zip code would have to be treated as a text string, or you would have to use “special” formatting.

Logical Test  A logical test is a statement that can be evaluated as either True or False, and often is used to evaluate or compare the value of a cell.  For example:  =A1>10.  This logical test will return TRUE if the inequality is true, or FALSE if the inequality is false.  In the Help utility, when referring to several logical statements, they will be written as “logical1, logical2….”, which means there are several logical tests, separated by commas.

Logical Statement A logical statement is statement that needs evaluation but will not necessarily return a TRUE or FALSE.  See IF command below.

Equal Sign  An = sign must be used to tell the program that you are entering something for evaluation such as a formula or a logical statement.  In the example above, failure to write =A1>10 would result, not in evaluation of TRUE or FALSE, but rather the writing of the alphanumeric statement A1>10.

LEFT(text, num_char)  The LEFT function returns the left-most characters of a string.  The first argument gives the location of the string, with the second argument of the command being how many characters you want to capture.

For example, =LEFT(A2,LEN(A2)-1) will return all of the characters of the string in location A2 except the last character since the number of characters captured is equal to the length of the string (LEN( ) ) less one.

RIGHT(text, number of characters)     The RIGHT function returns the right-most characters of a string.  The first argument gives the location of the string, with the second argument of the command being how many characters you want to capture.

For example, = RIGHT(A2,5) will return the right-most 5 characters from cell A2, and, = RIGHT(A2) will return the one right-most character from cell A2, since, if you do not specify an number of characters, it assumes 1 as default.

MID(text, start_number, number of characters)  The MID function returns some specified middle characters of a string in location text, starting with the start_number which is the first character of the MID string, and capturing the number of characters specified.  The first argument gives the location of the string, with the second argument of the command indicating where to start the MID string, and the last argument shows how many characters you want to capture.

For example, =MID(A2,3,5) would take 5 characters from the text located in A2, starting with the third character.

Concatenate    The CONCATENATE function combines multiple strings into a single string.  The syntax is, =CONCATENATE(text1, text2….) where text1, etc. can either be actual text in quotation marks or locations of text.  An empty set of quotation marks can be used to create a blank space, i.e., “ “.

For example, =CONCATENATE(A2, B2, C2)  would combine the three cells into the one where the concatenate command is entered. =CONCATENATE(“The average mileage for”, “ “,A2, “ “, is, “ “, B2, “ “, “mpg.”  The first output might look like:  The average mileage for Ford is 32.2 mpg. Where A column would be the list of manufacturers, and column B would be the average mileage.

If  The logical test IF, tests the contents of a cell and depending on the results of the test returns different values.  This function can be used with numeric or string data.

The syntax of the command is: =IF(logical test, value if true, value if false).

An example could be:  =IF(A2=”red”,1,0).  This would return 1 if the cell contents = red, and 0 otherwise.  This is helpful for counting how many times “red” appears in the examined cells.

Another example could be: IF(A2=25, “red”, “ “), which would enter red, if the content of cell A2 =25, otherwise it would leave the output blank.

And The logical test AND, tests logical statements, and depending on the results of the test returns TRUE or FALSE; TRUE of all logical statements are true, and FALSE otherwise.  The syntax is, =AND(logical1, logical2,…) .

For example, =AND(A2>85, A3= “History”) will return TRUE if both are true, otherwise FALSE.

Advertisements

Tags: , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: