Answer Happy

1 2 1000 GSA WN 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 ProviderID Provider Name 21 34-100288 a. patel, md 22 34-100206 a. q. noriega, md 23 34-100465 a. taji, md, inc. A B D Part 1: 15 points. TextFunctions Worksheet - Instructions • Review the data in the worksheet and make the conversions required to transform the data into a more usable form. For each transformation, you must use function(s). Insert new columns, as needed, write the necessary formulas - and leave the new columns in this worksheet. The formulas should be writing in Row 21-and copied down to all rows - producing the correct data. o The ProviderID is either a corporate tax-id (which is correctly formatted) or it is an individual's Social Security Number. Write a formula to either return the corporate tax-id, or build the SSN - inserting dashes to produce the 999-99-9999 format that is easier to read. o Display the Provider name in all caps. o Insert a new column- Provider Type - that will display "Medical Facility" if the word "hospital" is part fo the provider name, "Dental" for any provider with "dds" in the name. All other rows should be left blank. o Display the provider address without the "ADDRESS1:" prefix in the column. Convert the data to proper case. o Split the City and State data into separate columns. o Display only the standard 5-digit zip code. o Convert the Date Added column to display in the format of mm/dd/yyyy. (Be sure to take note of the different variations of this data in your formula. You may need to solve this one in multiple steps.) Copy/Paste (values) to display the data on the worksheet named "NewTextWorksheet". Format the data, to present a meaningful and an easy to consume report. Provider Address City, State sylvania, oh ADDRESS1: 5800 monroe st., bldg. e ADDRESS1: 6005 monclova road ADDRESS1: 3401 glendale, #212 maumee, oh toledo, oh in 66 F E G ** Hint: The TEXT() function has a for Date Added 10292021 8292021 8292021 Zip 43560-5704 43537-9030 43614-6735