Course Project

Part Two: Performing Text Management and Analytics

Instructions:

You are an analyst at an investment fund, ABC Capital LLC$.$ Your boss has assigned you with the task of coming up with a data table of all potential chemical industry companies that fit your company's financial investing criteria. Luckily, your boss has provided you with all the raw data, and all you need to do is organize it so your boss can forward the table to a server that will automatically contact the owners.

$1.$ Create a list of values separated by semicolons from the raw data by concatenating the contact's name $($be sure to split the contact name into first name and last name with a comma delimiter$),$ email, phone number, company name, position, city, state, country, and last updated. Separate with semicolons. Trim each text string in the concatenated value to remove all spaces except for spaces between words.

$2.$ Use the appropriate functions to identify the rows of data that hold the concatenated values in the correct order.

$3.$ Copy and paste the concatenated information as text into a new worksheet. Delete all invalid rows and columns that do not contain the information in the correct order. $($Hint: Sort the data.$)$

$4.$ Convert the remaining cells of data into a structured list with the data for the last name, first name, email, phone number, company name, position, city, state, country, and last updated appearing in different cells of the worksheet.

$5.$ Correct any errors in the data that were introduced during the conversion. For this exercise, simply delete rows that do not comply to the format you seek.

$6.$ Add a header row with labels to describe the data in each column.

$7.$ Change the data in the worksheet to an Excel table.

$8.$ Filter the results to only show companies in Minnesota.

$9.$ Add a total cell at the bottom that counts the number of records for MN$.$

$10.$ Save and close the worksheet.