Chapter 12 – 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 Chapter12_TextFunctions file.
Throughout this chapter…
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
Find the Locations worksheet to start learning and using these basic text functions.
- In cell B5, use TEXTBEFORE 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.
- In cell C5, use TEXTAFTER 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.
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. We will do the same thing for parts of an address.
- 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.
- 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
Let’s practice another way to separate strings in the Music worksheet, using the TEXTSPLIT function and the Spill feature of Excel.
- In cell B4, use TEXTSPLIT to split the contents of A4 into TWO values, which will be “spilled” into cells B4 and C4. Fill the function(s) down column B, which will also cause the spill to work all the way down column C.
SeparatingNames worksheet
- In the SeparatingNames worksheet, starting in cell B4, use a function or functions to split these names. You may use any of the functions we’ve learned so far.
- In cell D4, use CONCAT and LEFT to create a username for each of these people in the form of their first initial and last name. For example, Bill Smith would have the username BSmith.
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 with the proper date modified timestamp.
More Practice
Work you should do before our next class
Open the Chapter12_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.
Do NOT use a SPILL in this worksheet.
- In cell C4, 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.
- In cell D4, 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.
- In cell F4, 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. An example for Bill Smith with ID 88 from Hawaii would be BSmith88HA. Fill the function down column F to create a username for each customer.
ConsultantsAndFeedback worksheet:
You MAY use a SPILL in this worksheet.
- The Haptown consultants’ names are in columns A and B. In cell C4, combine them into the format of Last then First with a comma in between. For example, the consultant Bill Smith would appear as Smith, Bill. Fill the function down column C. You may need to adjust the width of column C so that each person’s name is visible.
- Column D contains a dump of feedback data from another application. In cell E4, use the appropriate function(s) to return the Feedback Topic, Feedback Submission Date, Consultant Last Name, and Consultant ID. Fill the function(s) down so that columns E thru H have the appropriate data.
Note that for the second option, you could use a combination of TEXTBEFORE and TEXTAFTER if you use the [instance_num] argument. A TEXTSPLIT will get this done much faster.
Pixar worksheet:
Do NOT use a SPILL in this worksheet.
- 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.
- Ensure you check your strings with LEN for hidden spaces and correct characters.
- 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.
- 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.
You may now save and close your Excel workbook. Make sure the most recently worked file is in your IU OneDrive with the proper date modified timestamp.