FORMULAS A TASK Name data to proper case Number of characters in a text string Find first 4 characters of a text string Find final 3 characters of a text string Get three characters from a text string starting on the third character Concatenate (bring together) text strings Concatenate (bring together) text strings adding a space, Concatenate (with space) using ampersands Convert a number (like a zip code) to text Convert a number (like a zip code) to text adding back leading zero Search for the location of the text string "@" within an email address Split off username from email address. Hint: search for @ symbol B FUNCTION TO LEARN DATA PRACTICE PROPER gabby conner LEN Gabby LEFT Conner RIGHT Conner MID Gabby CONCAT C3 and C4 CONCAT C3 and C4 C3 and C4 TEXT 53878 TEXT 4721 SEARCH gconner@nowhere.edu LEFT and SEARCH gconner@nowhere.edu #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Putting it all together Zip Codes as Numbers First Name Last Na Raw name data lillianna summers kallie cuadrado Try to use the functions above to obtain what is being asked in each column. blake may There may be more than one way to complete each task. Proper Name 46602 42599 5104 9611 24803 4579 47304 14422 28031 4143 estela guarino yasmin campbell ella chapman jovanni rhodes jaime english sara saunders ekaterina novikova When you complete a function, you will notice a link underneath. Follow that link if you need help with the function use and syntax. Tip: Use trial and error at first. Build complex formulas in pieces. 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:05 using the data in calumn C. Name data to proper case: Number of characters in a text string: Find first 4 characters of a text string: Find final 3 characters of a text string: 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: 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 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 Dz. Concatenate (bring together) text strings: 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 D. Concatenate (bring together) text strings adding a space," You can also concatenate by using 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, 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 type of data should actually be treated as text. Luckily, Excel as 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 Dio. Convert a number (like a zip code) to text: ( 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 zip code that is supposed have a leading zero, a slightly different formatting is required: "O***" which instructs Excel to format the text as 0 then the four numbers. Use that formatting technique in cell Dil. Convert a number (like a zip code) to text adding back leading zero: ) : 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 "e" 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: Finally, you can combine SEARCH and LEFT to isolate the username of the email address. SEARCH tells you where the "o" 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 username from email address. Hint: search for @symbol: FORMULAS A TASK Name data to proper case Number of characters in a text string Find first 4 characters of a text string Find final 3 characters of a text string Get three characters from a text string starting on the third character Concatenate (bring together) text strings Concatenate (bring together) text strings adding a space, Concatenate (with space) using ampersands Convert a number (like a zip code) to text Convert a number (like a zip code) to text adding back leading zero Search for the location of the text string "@" within an email address Split off username from email address. Hint: search for @ symbol B FUNCTION TO LEARN DATA PRACTICE PROPER gabby conner LEN Gabby LEFT Conner RIGHT Conner MID Gabby CONCAT C3 and C4 CONCAT C3 and C4 C3 and C4 TEXT 53878 TEXT 4721 SEARCH gconner@nowhere.edu LEFT and SEARCH gconner@nowhere.edu #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Putting it all together Zip Codes as Numbers First Name Last Na Raw name data lillianna summers kallie cuadrado Try to use the functions above to obtain what is being asked in each column. blake may There may be more than one way to complete each task. Proper Name 46602 42599 5104 9611 24803 4579 47304 14422 28031 4143 estela guarino yasmin campbell ella chapman jovanni rhodes jaime english sara saunders ekaterina novikova When you complete a function, you will notice a link underneath. Follow that link if you need help with the function use and syntax. Tip: Use trial and error at first. Build complex formulas in pieces. 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:05 using the data in calumn C. Name data to proper case: Number of characters in a text string: Find first 4 characters of a text string: Find final 3 characters of a text string: 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: 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 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 Dz. Concatenate (bring together) text strings: 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 D. Concatenate (bring together) text strings adding a space," You can also concatenate by using 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, 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 type of data should actually be treated as text. Luckily, Excel as 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 Dio. Convert a number (like a zip code) to text: ( 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 zip code that is supposed have a leading zero, a slightly different formatting is required: "O***" which instructs Excel to format the text as 0 then the four numbers. Use that formatting technique in cell Dil. Convert a number (like a zip code) to text adding back leading zero: ) : 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 "e" 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: Finally, you can combine SEARCH and LEFT to isolate the username of the email address. SEARCH tells you where the "o" 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 username from email address. Hint: search for @symbol