Review the data in the worksheet and make the conversions required to transform the data into a
Question:
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 – and looks like this: 34-123456) or it is an individual’s Social Security Number (entered as a 9 digit 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 of the provider name (found in the provider column), or will display “Dental” for any provider with “dds” in the name. All other rows should be left blank. Hint: The SEARCH and FIND functions return a value that indicates the position# where the searchValue is located in the searched string. If the Search or Find was unsuccessful, both functions return an error.
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. Display the City in proper case, the state as upper case.
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 – or with a function inside another function.) You may safely assume that the “dd” portion of the date is always represented with 2 digits.
• Copy/Paste (as values) to display the data on the worksheet named “NewTextWorksheet”.
• Format the data, to present a meaningful and an easy to consume report.
• You may not use the Text to Columns feature of Excel for any part of the Part 1 conversions!
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill