Using Concatenation to Create Mappable Addresses

Google Fusion Tables is pretty clever, and it can geocode most addresses to correctly place a marker on the map. So if your spreadsheet has a column with address data, you’re in business.

However, often the address data you have may be separated in several columns, like so:

Screen Shot 2013-09-27 at 4.34.32 PMThis shows nursing homes and their overall rating from AHJC, courtesy of your classmate, Eric Jankiewicz. Note how the address is split into Address, City, State, and Zip columns. You want to combine those columns and add commas between them.

Fortunately, you can combine the contents of cells with the CONCATENATE function. Concatenate means to combine. Use the Concatenate function to combine several pieces of text into one long piece of text. For example, =CONCATENATE(“hello”, ” friend”) results in the string, “hello friend”. The comma separates the different values that you want to combine.

In Excel, create a new column and enter

=CONCATENATE(C2,”, “,D2,”, “,E2,”, “,F2)

Screen Shot 2013-09-27 at 4.44.55 PM

 

Look carefully. The function combines the contents of C2 with a comma and a space, then adds the contents of D2, then another comma and space, then the contents of E2, and so on.

The result:

Screen Shot 2013-09-27 at 4.51.22 PM

 

Double-click the box in the lower-right corner of the cell to auto-increment the function for all the rest of your rows.

Screen Shot 2013-09-27 at 4.53.40 PM

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *