Lab $-$ Preparing Data

Objectives

Part $1$: Import the Data into Microsoft Excel

Part $2$: Get the Month out of a Date

Part $3$: Split Full Name Column into Different Columns

Part $4$: Concatenate Columns

Background $/$ Scenario

Preparing the data is a critical step in data analysis. You may receive data from different sources and you will

need to manipulate the data and extract the necessary information.

In this lab, you will explore how to use the look up information function. You will also explore how to split and concatenate columns of data.

Required Resources

$1$ PC with Microsoft Excel installed $($Other spreadsheet programs, such as LibreOffice or Google Sheets, may be used, but they may not have the same formula syntax$)$

Part $1$: Import the Data into Microsoft Excel

Step $1$: Create a $.$csv file.

a$.$ Open text editor and paste the following text.

Full Name, Join Date

Olivia Jones, $02/28/2010$ Lucas Smith, $9/14/2011$ Ava Williams, $4/12/2012$ Liam Johnson, $7/31/1999$

b$.$ Save the file as a $.$csv$.$

Step $2$: Import the $.$csv file into Excel.

a$.$ Open Excel.

b$.$ Select Data from the menu. Select Get External Data $>$ From Text.

c$.$ Select the saved.csv file and click Import.

d$.$ The Text Import Wizard opens. In the Step $1$ of $3$ dialog box, select the My Data has headers checkbox.

Click Next.

e$.$ In the Step $2$ of $3$ dialog box, unselect Tab checkbox and select the Comma checkbox. Click Next.

f$.$ Select the Join Date column, and select Date. Verify that the dropdown displays MDY$.$ Click Finish.

g$.$ Click OK to open the data in the existing worksheet.

$2017$ Cisco and$/$or its affiliates. All rights reserved. This document is Cisco Public.

Page $1$ of $4$

Lab $-$ Preparing Data

Part $2$: Get the Month out of a Date

Excel has other functions that allow you to parse out the information from a date. Furthermore, it allows you to lookup values in a different table. In this part, you will extract the month out of a date and use vlookup to convert it into a string that spells out the name of the month.

The MONTH function returns the month as a number from $1($January$)$ to $12($December$).$ VLOOKUP function looks for a value in the leftmost column of a table and returns a value in the same row from the column you

chose.

Step $1$: Add a lookup table.

a$.$ To lookup the month, a table with the name of each month is created. In a new sheet, enter $1-12$ the

first column and the name of month in the second column.

b$.$ Rename Sheet$2$ to Months.

Step $2$: Parse the month information from the date.

In an empty cell in column Crow $*2$ enter $=$month$($B$2).$ The data in cell B$2$ is $2/28/2010.$ What did this formula

return? Verify the number format is set to General $($Right$-$click Format Cells $>$ General$)$