Excel Tips and Tricks for Prepping Your Data

In this article we will outline useful features in Excel that will help you prep your data for import. Use Text to Columns and Concatenate to make sure the voters' addresses are properly formatted for importing. Addresses must be separated into three fields: 1)house/street number 2)street name 3)apartment.

Text to Columns

Text to Columns is a tool that will let you separate your apartment numbers and street numbers from the rest of the address. 

Starting Point

 

 

We are starting with your address information all together. Using Text to Columns we will separate out the street number and the apartments. 

Step 1: Separating units

First insert an empty column to the right of the column with the addresses. To do this you can right click on the neighboring column (B in this case) and select insert. 

Navigate up to the Data at the top of your screen and select Text to Columns in the drop down.

The following box will pop up. 

Select Delimited and click Next. On the next screen (below) select the Other check box and deselect any other boxes. Enter the pound sign in the space provided and click Finish. 

The unit numbers/letters will be moved to the next column over. 

Step 3: Separating Street Number

Before you separate out the elements of street name, you need to add several empty columns to the right of column A. There are many elements to the contents of column A and they will all be separated into several columns. We suggest inserting 10 blank columns to the right of column A. It should look something like the below image. 

 

Once again, select the contents of column A by using command, shift, and the downward arrow. Navigate to Text to Columns once again. Select Delimited and click next. 

Select the Space check box and unselect any other boxes. Click Finish. 

Your spreadsheet should now look like the image below. 

 Step 4: Use Filtering to Clean Up

 Now we can go back through our separated columns and clean up our work. First you can delete empty columns at the end. Caution: Just because a column looks like it is empty does not mean it is! The first several rows that you see may be empty, but later on, maybe hundreds of rows later, there may be data. To prevent data loss, use filters to check the column. First click the box in the corner between row 1 and column A

Navigate up to Data at the top of your screen and select Filter. 

The filter button will appear for all columns.

Now in the columns that appear blank click that downward arrow filter button. In columns that are complete blank the only filter to sort by will be (Blank). You can delete these columns.

 

If a column is not blank you will see the data that populates populated boxes. Such as in my column F. 

If you unselect (Select All) and (Blanks) you can see just the row with the data. 

Now we see a unit that we were unable to separate before. Manually take out 200 and put it in the appropriate column. You can also delete Ste. while you're at it. Go through your columns until you have deleted all blank columns and fixed any stray units. To see all rows again just reselect (Select All). To stop filtering and remove the downward arrow filter icons go back up to the Data tab and deselect filter.

When finished, your spreadsheet should look like the image below. As you can see I've added some headings just for clarification. 

Now you can move onto the next section Concatenate to learn how to combine the different elements of the street name. 

 

Concatenate

Concatenate is a tool that combines columns. Use the equation =CONCATENATE() to combine separate parts of the street name into a single column. Here is a step by step guide to using concatenate to combine your street name elements.

Starting Point

 

These are the values you start out with. All elements of the street name are separated since you separated out the street number in the previous tutorial on using Text to Columns. 

Step 1: Insert a new column

You will need a new column where your combined data will go. Insert a new column to the left of your elements by right clicking on the first element heading (B in this case) and clicking insert.

Step 2: Create Equation

In the second box of the new column you created you will enter the following equations

=CONCATENATE()

within the parentheses you must enter all the boxes that you want to be combined. In my example I am combining the second row of columns B, C, D, and E, so the boxes I want to combine are B2, C2, D2, and E2. Each of these values must be separated in the equation by the symbols within the following brackets: {," ",}. The quotation marks with the space between them are representative of the spaces that you want between the combined elements of the street name. After entering the equation like the example above you can press enter.

You will now see that the first row has been concatenated.

Step 3: Copy Equation

Click on the concatenated box and copy the contents. A dashed line should appear around that box. 

Step 4: Select Column

 

To copy the equation through the length of the column you will need to select all the appropriate rows in column A. Though that would be easy to do manually in my sample that only has 4 entries, but in your real data you likely have thousands of entries. Scrolling down to select them all would be time consuming and inefficient. Instead click over to a column where there is an entry on every line. I suggest using the column with last names or house numbers. For this example I am using the column with street names. 

Click on the first box in the selected column. While holding down shift and command at the same time, press the down arrow on your keyboard. This will select the entire column like in the above image.

Click on the final box of the highlighted column like in the above image. Now use the left arrow button to click over to what would be the final box of the concatenated column. It should now look like the below image. 

You will now use the same method as before for highlighting the whole column. While holding down the shift and command buttons press the up arrow. This will highlight the whole concatenating column. 

Step 5: Paste Column

Now that the whole column is selected you can paste the equation. Use command+V to paste the concatenation equation you copied earlier in the column. Refer to the below image. 

Right now each of these boxes is defined by an equation. We want them populated by values instead. To do this copy the entire column using command+C. Then right click on the column and select Paste Special. 

A box will pop up. Select 'Values' in the Paste section and click OK. 

Step 6: SAVE

All you have to do now is save your work! Use command+S to save your work and you're done concatenating!