In the same Excel data file, on the tab labeled Case $2$ solution, you should create your answer.

$$ Copy the data into this sheet in columns A$,$ B and C$.$

$$ Fill out the remaining columns with missing information in the following way:

Analytics mindset case studies $$ ETL $$ Case $2$ Text extraction and unique identifiers $$ Excel $1$

$2019$ Ernst & Young Foundation $($US$).$ All Rights Reserved.

SCORE No$.06753-191$US$\_21$

$$ For columns D$,$ E$,$ F$,$ G and H$,$ separate the values using the text$-$to$-$columns and the fixed$-$

width delineation. You should make sure the data in each column matches the column

heading. For column H$,$ LocationPlantID, you should combine the fields Location and PlantID

so the output looks like USA$-12.$

$$ For columns J$,$ K$,$ L$,$ M and N$,$ use formulas to extract the correct information from the

EmployeeCode $($you must keep the contents of the cells as formulas$).$ For column N$,$

LocationPlantID, you should combine the fields Location and the PlantID so the output looks

like USA$-12.$

$$ Consider using the following functions for each column $($there are simpler methods for

some of these, but this suggestion is meant to teach you various ways of doing this task$)$:

$$ Location: Use the LEFT function.

$$ EmpID: Use the MID function.

$$ PlantID: Use the MID and FIND functions. For the FIND function, set the function to

search for the hyphen character $(-)$ and then return the values relative to the position

of the character.

$$ PayPeriod: Use the RIGHT function.

$$ Submit your updated Excel file as your final deliverable with the correct data in each of the columns

from D through N$.$ As an example of your final deliverable, the first seven rows should look like the

table below.

Analytics mindset case studies $$ ETL $$ Case $2$ Text extraction and unique identifiers $$ Excel $2$

$2019$ Ernst & Young Foundation $($US$).$ All Rights Reserved.

SCORE No$.06753-191$US$\_21$

EmployeeCode FirstName LastName Location EmpID Plant ID Pay Period LocationPlantID Location EmpID Plant ID Pay Period LocationPlantID

JAP$1080-19$M Carl Rodriguez JAP $108019$ M JAP$-19$ JAP $108019$ M JAP$-19$

AUS$1241-21$M Anne Wright AUS $124121$ M AUS$-21$ AUS $124121$ M AUS$-21$

AUS$1297-21$M Patrick Rivera AUS $129721$ M AUS$-21$ AUS $129721$ M AUS$-21$

GER$1332-21$W Ruby Walker GER $133221$ W GER$-21$ GER $133221$ W GER$-21$

GER$1202-10$M Teresita Guillen GER $120210$ M GER$-10$ GER $120210$ M GER$-10$

ARG$1028-20$M Earl Morgan ARG $102820$ M ARG$-20$ ARG $102820$ M ARG$-20$

CAN$1344-19$W Joella Causey CAN $134419$ W CAN$-19$ CAN $134419$ W CAN$-19$

Solution Using Text$-$to$-$Columns Solution Using Formulas