Data Analytics Modelling

Lab Notes (2022-23)(Q26-Q30)

B.Com ( Business Analytics)

Sem III

V. Table for questions 26 to 30:

        26.From the above table, remove the invalid phone number   which             are not in the proper format of having 10 digits, no characters                 and the phone numbers which are starting with 9/8/7/6

        Step1: Copy the contents of phone number in a new sheet.

        Step2: Copy the contents of phone number in another column.

        Step3: Select the contents and go to conditional formatting.

        Step4: In the drop down box , select Between.

        Step5: In the dialogue box, fill it with 5999999999 and 9999999999                             click ok.

        Step6:All the numbers which starts with 6,7,8,9 will be highlighted.

        Step7: Go to sort and filter, Select filter.

        Step8:In the dialogue box opened select filter by colour and click ok.

        Step9: Phone numbers with the required format will be selected.

        27.From the above table, put the phone numbers in US format.

            Step1: Copy the contents of the Phone number in a new sheet.

            Step2:  Copy the contents and paste in a new column

            Step3:  Select the contents.  On Home tab,  Click on Format.

            Step4: In the drop down menu , select format cells.

            Step5: A dialogue box opens.  Select Custom.

            Step6: In the right side of that dialogue box, enter +1(###)-###-#### and                             click ok

           Step7: All the contents of the phone number will be converted in the said                               format ( US format)

        28.From the above table, split the Address column into Address, City,                     State and ZIP  code.

            Step1: Copy the contents of Address Column in a new sheet.

            Step2: Use =PROPER(C4) in order to make the first letter of the word in                                 Upper case.

            Step3: Select C4 and drag down.

            Step4: Select the contents of Address column , Go to Data tab, click on Text                         to Columns.

            Step5: In the dialogue box , click next, select comma, click next and then                             click finish.

            Step6: The Address Column is split as required.

        29.From the above table, select only the middle name from customer                     name.

            Step1: Select the contents of customer name in a new sheet.

            Step2: In cell B4  write the syntax, =MID(A4, FIND(" ",A4)+1,20)

            Step3: Select B4 and Drag Down.  This given the middle with the last names.

            Step4: Select cell C4 and write the syntax , =LEFT(B4, FIND(" ",B4))

            Step5: Select cell C4 and drag down to get all the middle names.

        30. From the above table, calculate the number of days required for                         shipment.

        Step1: Select the Shipment date and Order date columns.

        Step2: Since Shipment date should be after Order date, paste the shipment date                     again after order date.

        Step3: In Cell D4 write the syntax , =DATEDIF(B4,C4,"D") and enter.

        Step4: Select D4 and drag down.  We get the number of days to shipment.