Chapter 8 – Text Functions

INTRODUCTION

Many times we are not working with numbers in a spreadsheet but text data. And we will inevitably have to work with this text for cleaning up dirty data or rearranging information for a variety of reasons (e.g., preparing data for import into a database).

  • Use text functions such as LEN, TEXTBEFORE, TEXTAFTER, and TEXTSPLIT to get substrings of text data
  • Use concatenation (CONCAT) to combine several segments of text

Guided Exercises

Open the Chapter08_TextFunctions file.

Text Functions

  • TEXTBEFORE and TEXTAFTER returns the text before or after a string of text – known as a delimiter.
  • TEXTSPLIT separates text into rows or columns using delimiters.
  • LEFT, RIGHT, and MID return some number of characters from the beginning, end, or middle of a string of text.
  • LEN returns the length of a string, or how many characters it has.
  • CONCAT combines text elements to create a single string of text. The & symbol also works.

Locations worksheet

  1. In cell B5, use the appropriate function(s) to return just the city from the beginning of the text string in cell A5. Fill the function down column B to return each city. Check your work using a LEN function in a blank cell on the worksheet.
  2. In cell C5, use the appropriate function(s) to return just the state abbreviation from the end of the text string in cell A5. Fill the function down column C to return each state abbreviation. Check your work using a LEN function in a blank cell on the worksheet.

Joining Pieces of Data

On the Concatenation worksheet, the phone numbers of some customers are listed, but the three parts of the phone number have been listed separately. You need to join the three pieces together so that the area code is in parentheses, followed by a space, the exchange number, a hyphen, and finally the line number, just like the example given in cell D3.

  1. In cell D4 use the CONCAT function to join the text strings in row 4 so that the phone number appears like the example: (812) 855-4848. Fill the function down column D to return each phone number.
  2. In cell I4, use the CONCAT function to join the City, State, and Zip Code so that they appear one cell, such as: Washington, DC 20009. Fill the function down column I.

Music worksheet with TEXTSPLIT

  1. In cell B2, use the appropriate function(s) to return just the song title from cell A2. Fill the function(s) down column B. Check your work using a LEN function in a blank cell on the worksheet.
  2. In cell C2, use the appropriate function(s) to return just the artist’s name from cell A2. Fill the function(s) down column C. Check your work using a LEN function in a blank cell on the worksheet.

If you are going to stop working here, make sure your file is saved. When you close it, if there is any unsaved work, Excel will prompt you to save. Make sure the most recently worked file is in your IU OneDrive.

More Practice

Work you should do before our next class

Open the Chapter08_TextFunctions file.

The CustomerData worksheet contains some data about the customers of a business. Having the entire name in one cell (column B) makes it difficult to sort the data. This is also true for having both the city and the state in one cell (column E).

  1. In cell C5, use the appropriate function(s) to return just the customer’s first name from the beginning of the text string in cell B5. Fill the function(s) down column C to return each customer’s first name. Check your work using a LEN function in a blank cell on the worksheet.
  2. In cell D5, use the appropriate function(s) to return just the customer’s last name from the end of the text string in cell B5. Fill the function(s) down column D to return each customer’s last name. Check your work using a LEN function in a blank cell on the worksheet.
  3. In cell F5, use the appropriate function to create a username for each customer. The username should begin with the first letter of the person’s first name, then the entire last name followed by the CustomerID and ending with the state abbreviation. Fill the function down column F to create a username for each customer.

Clients&Products worksheet:

  1. The customers’ last names are in column B and first names are in column C. Beginning in cell D6, join the names together so that the first and last name are separated by a single space. If you do this correctly, the value in cell D6 will be Mark Baker. Fill the function down column D. You may need to adjust the width of column D so that each person’s name is visible.
  2. Column E contains the Product Code and Product Name separated by a hyphen. In cell F6, use the appropriate function(s) to return just the ProductCode from the data in cell E6. Fill the function(s) down column F.
  3. In cell G6, use the appropriate function(s) to return the ProductName from the string of characters in cell E6. Fill the function(s) down column G.

Pixar worksheet:

  1. In cell B4, use the appropriate function(s) to return just the film name from the beginning of the text string in cell A4. Fill the function down column B. In an empty cell somewhere on the worksheet, make sure to use a LEN function to check how many characters are returned to you in cell B4. Count them yourself to make sure there aren’t any extra spaces being returned that shouldn’t be there.
  2. In cell C4, use the appropriate function(s) to return just the character name from the end of the text string in cell A4. Fill the function down column C. Again, in an empty cell somewhere on the worksheet, make sure to use a LEN function to check how many characters are returned in any of the cells in column C. Count them yourself to make sure there aren’t any extra spaces being returned that shouldn’t be there.
  3. In cell E4, join the film name, character name, and doll price as well as punctuation and spaces, according to the example given in cell E3. Fill down column E.

 

License

K160: Introduction to Modeling and Solving Business Problems 1e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.