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: 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 D7. 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 D8. 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 types 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 D10. 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 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 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 "@" 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 "@" 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. D2 X✓ fx A 1 TASK 2 Name data to proper case 3 Number of characters in a text string 4 Find first 4 characters of a text string B C D E F G H FUNCTION TO LEARN DATA PRACTICE FORMULAS PROPER hayden anderson #N/A LEN Hayden #N/A LEFT Anderson #N/A RIGHT Anderson #N/A MID Hayden #N/A CONCAT C3 and C4 #N/A CONCAT C3 and C4 #N/A C3 and C4 #N/A TEXT 51085 4033 #N/A #N/A handerson@nowhere.edu handerson@nowhere.edu #N/A #N/A 5 Find final 3 characters of a text string 6 Get three characters from a text string starting on the third character 7 Concatenate (bring together) text strings 8 Concatenate (bring together) text strings adding a space, 9 Concatenate (with space) using ampersands 10 Convert a number (like a zip code) to text 11 Convert a number (like a zip code) to text adding back leading zero 12 Search for the location of the text string "@" within an email address 13 Split off username from email address. Hint: search for @ symbol 14 TEXT SEARCH LEFT and SEARCH 15 Raw name data Zip Codes as Numbers Proper Name First Name 36446 29857 2319 31579 4132 66747 27734 35681 31556 2605 16 Putting it all together 17 18 Try to use the functions above to obtain what is being asked in 19 each column. 20 21 There may be more than one way to complete each task. 22 23 When you complete a function, you will notice a link underneath. 24 Follow that link if you need help with the function use and syntax. 25 26 Tip: Use trial and error at first. Build complex formulas in pieces. kallie cuadrado blair tindall jovanni rhodes olivia bradley mya brown samantha ward nadene senger ella byrne rose williamson val elliott 33 35 36 38 39 40 45 46 48 49 22222222223↓EEEEEE777144747 28 29 30 Last Name Short Name (First L.) New Zip Proper Name First Name Last Name Short Name New Zip #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A

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: 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 D7. 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 D8. 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 types 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 D10. 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 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 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 "@" 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 "@" 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. D2 X✓ fx A 1 TASK 2 Name data to proper case 3 Number of characters in a text string 4 Find first 4 characters of a text string B C D E F G H FUNCTION TO LEARN DATA PRACTICE FORMULAS PROPER hayden anderson #N/A LEN Hayden #N/A LEFT Anderson #N/A RIGHT Anderson #N/A MID Hayden #N/A CONCAT C3 and C4 #N/A CONCAT C3 and C4 #N/A C3 and C4 #N/A TEXT 51085 4033 #N/A #N/A handerson@nowhere.edu handerson@nowhere.edu #N/A #N/A 5 Find final 3 characters of a text string 6 Get three characters from a text string starting on the third character 7 Concatenate (bring together) text strings 8 Concatenate (bring together) text strings adding a space, 9 Concatenate (with space) using ampersands 10 Convert a number (like a zip code) to text 11 Convert a number (like a zip code) to text adding back leading zero 12 Search for the location of the text string "@" within an email address 13 Split off username from email address. Hint: search for @ symbol 14 TEXT SEARCH LEFT and SEARCH 15 Raw name data Zip Codes as Numbers Proper Name First Name 36446 29857 2319 31579 4132 66747 27734 35681 31556 2605 16 Putting it all together 17 18 Try to use the functions above to obtain what is being asked in 19 each column. 20 21 There may be more than one way to complete each task. 22 23 When you complete a function, you will notice a link underneath. 24 Follow that link if you need help with the function use and syntax. 25 26 Tip: Use trial and error at first. Build complex formulas in pieces. kallie cuadrado blair tindall jovanni rhodes olivia bradley mya brown samantha ward nadene senger ella byrne rose williamson val elliott 33 35 36 38 39 40 45 46 48 49 22222222223↓EEEEEE777144747 28 29 30 Last Name Short Name (First L.) New Zip Proper Name First Name Last Name Short Name New Zip #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A

Quickbooks Online Accounting

3rd Edition

ISBN:9780357391693

Author:Owen

Publisher:Owen

Chapter8: Budgets And Bank Reconciliations

Section: Chapter Questions

Problem 2.7C

See similar textbooks