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. 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 thought each column logically using trial and error if necessary. Hint: The last column for 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 Val Last Name Short Name (First L.) New Zip Code D2 ☑✓ 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 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 LEFT and SEARCH 51085 4033 handerson@nowhere.edu handerson@nowhere.edu #N/A #N/A #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 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. 27 kallie cuadrado blair tindall jovanni rhodes olivia bradley mya brown samantha ward nadene senger ella byrne rose williamson val elliott 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 Sheet1 + Workbook Statistics EL F G H 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 Give Feedback to Microsoft 100% +

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. 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 thought each column logically using trial and error if necessary. Hint: The last column for 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 Val Last Name Short Name (First L.) New Zip Code D2 ☑✓ 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 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 LEFT and SEARCH 51085 4033 handerson@nowhere.edu handerson@nowhere.edu #N/A #N/A #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 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. 27 kallie cuadrado blair tindall jovanni rhodes olivia bradley mya brown samantha ward nadene senger ella byrne rose williamson val elliott 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 Sheet1 + Workbook Statistics EL F G H 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 Give Feedback to Microsoft 100% +

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