MyEducator Tasks Use time and text functions to complete the tasks below. 2.1 Use the LEN 2.2 Use the SEARCH 2.3 Use the LEFT function in cell C5 to return the text "Employee number " from the "Text Stamp Phrase" in cell C2. Notice the space after number. a. Use a reference to the location of the "#" symbol in cell C4 as the "[num_chars]" argument. b. Since the "#" symbol is one character past the text you want to return, you will need to adjust the "[num_chars]" argument by subtracting 1 from the reference to cell C4. Text Stamp Phrase: Employee number # entered the server room at HH:MM:SS today. Phrase Part 1 Employee number Employee Number Location of "#" 17 Phrase Part 1 =LEFT(C2,C4-1)&"Employee Number" Location of "HH" Phrase Part 2 Phrase Part 3 Employee Entry Swipe Hour Minute Second 2 Dec Hour 18 0.03683 00 43 0.07735 00 47 0.07851 00 33 0.11561 00 9 0.12751 00 40 0.13393 00 31 0.15009 00 35 0.15765 00 34 0.1589 00 7 0.16495 00 14 0.17773 00 32 0.19499 00 26 0.20724 00 31 0.22585 00 2 0.23963 00 49 0.26752 00 1 0.27961 00 23 0.29132 00 3 0.34089 00 5 0.34756 00 43 0.36121 00 49 0.47293 00 34 0.47747 00 43 0.50246 00 20 0.50331 00 12 0.51943 00 26 0.53676 00 10 0.53725 00 44 0.53871 00 29 0.55506 00 28 0.58649 00
Added by Fernando H.
Close
Step 1
Step 1: Identify the text you want to extract, which is "Employee number" from the text in cell C2. Show more…
Show all steps
Your feedback will help us improve your experience
Kirsty Gledhill and 74 other Intro Stats / AP Statistics educators are ready to help you.
Ask a new question
Labs
Want to see this concept in action?
Explore this concept interactively to see how it behaves as you change inputs.
Key Concepts
Recommended Videos
You are responsible for monitoring employees' entry into your company's server room. To complete this task, you need to review a log of when employees entered the room. The log is a little bit difficult to read. It contains: An employee number (column B). A computer generated stamp that records when the employee swiped their ID card to enter the room (column C). Ultimately, you want to create the "Text Stamp" in column K that completes the phrase, "Employee number # entered the server room at HH:MM:SS today." for each row of the log. You will replace "#" with the employee number and "HH:MM:SS" with the appropriate time stamp. Use time and text functions to complete the tasks below. 2.1 Use the LEN function in cell C3 to calculate the length of the "Text Stamp Phrase" in cell C2. 2.2 Use the SEARCH function in cell C4 to determine the position of the "#" symbol in the "Text Stamp Phrase" in cell C2. 2.3 Use the LEFT function in cell C5 to return the text "Employee number " from the "Text Stamp Phrase" in cell C2. Notice the space after number. Use a reference to the location of the "#" symbol in cell C4 as the "[num_chars]" argument. Since the "#" symbol is one character past the text you want to return, you will need to adjust the "[num_chars]" argument by subtracting 1 from the reference to cell C4. 2.4 Use the SEARCH function in cell C6 to determine the position of the characters " HH" (notice the space before the first H) in the "Text Stamp Phrase" in cell C2. 2.5 Use the MID function in cell C7 to return the text " entered the server room at " (notice the spaces at the beginning and end of the phrase) from the "Text Stamp Phrase" in cell C2. Use a reference to the location of the "#" symbol in cell C4 as the "start_num" argument. Since the "#" symbol is 1 character before the text you want to return, you will need to adjust the "[start_num]" argument by adding 1 to the reference to cell C4. Use the difference between the location of the "#" symbol (cell C4) and the characters " HH" (cell C6) as the "num_chars" argument. 2.6 Use the RIGHT function in cell C8 to return the text " today." from the end of the "Text Stamp Phrase" in cell C2. The "[num_chars]" argument for your function is 7 since there are seven characters in the text " today." (including the space at the beginning). 2.7 Use the HOUR function in cell D12 to calculate the "Hour" portion of the "Entry Swipe" found in cell C12. Copy and paste the function down to complete the "Hour" column of the table. 2.8 Use the MINUTE function in cell E12 to calculate the "Minute" portion of the "Entry Swipe" found in cell C12. Copy and paste the function down to complete the "Minute" column of the table. 2.9 Use the SECOND function in cell F12 to calculate the "Second" portion of the "Entry Swipe" found in cell C12. Copy and paste the function down to complete the "Second" column of the table. 2.10 Use the CONCAT function in cell J12 to combine the text in cells G12, H12, and I12 to create a "Time Stamp". Notice that the syntax for the "Time Stamp" is "HH:MM:SS". You will need to insert the ":" symbol between "Hour" and "Minute" and between "Minute" and "Second". Hint: you should have five arguments for your function. arguments 2 and 4 should be the ":" symbol. Copy and paste your function to complete the "Time Stamp" column of the table. 2.11 Use the CONCAT function to combine the text in cells C5, B12, C7, J12, and C8 (in that order) to create the "Text Stamp" in cell K12. The "Text Stamp" in cell K12 should read "Employee number 18 entered the server room at 00:53:02 today." Use absolute cell reference when necessary so that the function can be reused. Copy and paste your function to complete the "Text Stamp" column of the table.
Supreeta N.
Using absolute cell references as necessary, in cell G4, construct a formula to calculate the percent that the Colorado Total is of Total Sales, and then apply Percent Style with zero decimals. Fill the formula down through the range G5:G8. I need help writing the formula for this question. Where do I type everything? Step by step instructions would be helpful as to where to type and what to press/click. Ignore the selected cells in the screenshot, please. Thank you!
Akash M.
Introduction Frequently, you will have data that, instead of being numerical, is text-based. The data could be names of people, businesses, or longer-form text you would like to mine for information. While Excel is primarily designed to deal with and manipulate numbers, it also has a set of tools that help you work with text information efficiently and easily. This tutorial is designed to familiarize you with those tools in a common and easy-to-understand situation. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Common Text Functions Functions such as PROPER, LEN, LEFT, and RIGHT allow you to clean up and parse text information that may arrive to you in a less than ideal format. Practice using these functions in cells D2:D5 using the data in column C. Name data to proper case: fill in the blank 2 Number of characters in a text string: fill in the blank 3 Find the first 4 characters of a text string: fill in the blank 4 Find the final 3 characters of a text string: fill in the blank 5 While LEFT and RIGHT are straightforward, the MID function will provide the middle portion of a text string. You just need to tell it where to begin and how many characters you want after that location. Get three characters from a text string starting on the third character: fill in the blank 6 Concatenation Concatenation is a very common task in Excel (and in many other programming languages). To concatenate means to bring or "stick" things together. A common example is to have a first name and last name and a need to bring those together for a full name. In Excel, there are several ways to concatenate text strings. The easiest is the CONCAT function. All you do is tell it which cells you want stuck together. Try doing that in cell D7. Concatenate (bring together) text strings: fill in the blank 7 However, CONCAT is very literal. It doesn't know that there should be a space between a first and last name, for example. Therefore, you need to add that space to the CONCAT function. In Excel, a blank space is represented by the " " string of characters; quote-space-quote. Add that to the CONCAT function and practice in cell D8. Concatenate (bring together) text strings adding a space, " ": fill in the blank 8 You can also concatenate by using the ampersand (&) calculation operator instead of the CONCAT function. Again, include a space represented by the " " into your formula. Practice that in cell D9. Concatenate (bring together) text strings using ampersand (&) and adding a space, " ": fill in the blank 9 Number Conversion Not all numbers should be treated as "numbers" in a dataset. For example, zip codes, phone numbers, addresses, etc., are not meant to be added, subtracted, divided, or multiplied. Adding zip codes makes no sense. Therefore, those types of data should actually be treated as text. Luckily, Excel has the TEXT function which will do that easily. The TEXT function also requires that you tell Excel how to format the result. In the case of a zip code, we want five characters: "#####" (use the quotation marks). Use the TEXT function and the "#####" formatting rule to convert the number in C10 to a text zip code in cell D10. Convert a number (like a zip code) to text: fill in the blank 10 One challenge with converting zip codes and other numbers is that some of those values start with a leading zero. For numbers, a leading zero does not mean anything. However, for a zip code, it means a lot! Therefore, when converting a zip code that is supposed to have a leading zero, a slightly different formatting is required: "0####" which instructs Excel to format the text as 0 then the four numbers. Use that formatting technique in cell D11. Convert a number (like a zip code) to text adding back the leading zero: fill in the blank 11 Searching and Splitting Sometimes you need to search for the location of the first occurrence of a character or sub-string within a larger string. A common example is to find the location of the "@" symbol in an email address so you can isolate the username of the address. In cell D12, find the location of the "@" symbol in the email address in cell C12. Search for the location of the text string "@" within an email address: fill in the blank 12 Finally, you can combine SEARCH and LEFT to isolate the username of the email address. SEARCH tells you where the "@" symbol is located and LEFT needs to know how many characters on the left you want to keep; which SEARCH can provide. Try combining SEARCH and LEFT in cell D13 to isolate the email username from cell C13. Note: there is one slight modification you will need to make to the SEARCH part of the formula to get just the username. See if you can figure it out. Split off the username from the email address. Hint: search for the @ symbol: fill in the blank 13 Text Data Cleaning and Manipulation Beginning in row 16, columns B and C contain raw names and numeric zip codes. In columns D through H, you are asked to clean and manipulate that data in specific ways. The good thing is that you only have to create formulas for the first row, then you can fill down. Enter the values for the last row below. Note: These are meant to be challenging. So just take your time and work through each column logically using trial and error if necessary. Hint: The last column for the zip code will require a nested IF function to first see if the numeric zip code is 5 or 4 characters long so you then know which formatting to apply. Proper Name First Name Last Name Short Name (First L.) New Zip Code
Ayushi S.
Recommended Textbooks
Elementary Statistics a Step by Step Approach
The Practice of Statistics for AP
Introductory Statistics
Transcript
18,000,000+
Students on Numerade
Trusted by students at 8,000+ universities
Watch the video solution with this free unlock.
EMAIL
PASSWORD