- Download the Lab 4 Start file . You will notice you have data but most of it is mixed together in column B. Your assignment is to use formulas to place this data into different columns. The columns you will need are as follows:
- First make a copy of the sheet called "RAW" and call it "Build", do all your work in the build sheet, this allows you to back up and start over if you make a mistake that destroys data.
- In Column C - Strip the date information off the left side of column B.
- In Column D - Use the "DATEVALUE" function to turn the data in column C into a valid date.
- In Column E - Strip the Transaction Number out of Column B, the transaction number starts with "P00" and is 12 characters long.
- In Column F - Use the MID function to Strip the name information out of column B. You will notice there is no absolutly clean way to do this, we will fix this in column G.
- In Column G - Use the IF function to clean up the data in Column F. Your result should have as much of the name as you can get, but have no numbers on the end.
- In Column H - There are 5 columns of dollar amounts near the right end of column B. Try to strip them out and put them in Column H. It does not have to be perfect at this point, but the better you are here the easier it will be in the next step.
- In Column I - Take the first dollar amount out of column H and put it here as a number. Note: you will need to use the VALUE function to do this.
- In Column J - Take the Second dollar amount out of column H and put it here as a number. Note: you will need to use the VALUE function to do this.
- In Column K - Take the Third dollar amount out of column H and put it here as a number. Note: you will need to use the VALUE function to do this.
- In Column L - Take the fourth dollar amount out of column H and put it here as a number. Note: you will need to use the VALUE function to do this.
- In Column M - Take the fifth dollar amount out of column H and put it here as a number. Note: you will need to use the VALUE function to do this.