محلل بيانات

All the following instructions are to be carried out in the HR Data sheet. To get help with the more difficult questions click on the Hint to the right.

1)In C4, in the HR Data sheet join the data in A4 and B4 to create the employee ID F1180. Copy the formula down for the rest of the staff.

2)In K4, extract just the floor number from J4 (first 2 numbers) and then copy it down.

3a)In F4, generate the full name for each staff member by joining the first name, a space and the surname.

b)Add another function to the calculation in F4 so that all the names show in Proper case and then copy the calculation down.

4a)In G4, generate an email address by joining the first letter of the first name, the surname and "@zenco.com" to get Sbacata@zenco.com.

b)Adjust the calculation in G4 so that the email is shown in all lower case and copy it down.

5a)In L4, extract the text between the two dashes (West) in Location then copy down to make sure it works for all staff.

b)Adjust the formula in L4 so that all instances of NorthEast are replaced with just North and copy down again.

6a)In M4, extract the last 4 characters from the Location in J4.

b)You will notice that instead of getting four numbers you only got 3 or in some cases 2, adjust the formula so that you get the 4 digit extension code in each case.