Split values in Excel for geocoding

When geocoding an Excel or Google spreadsheet, it's crucial to utilize as much address information as possible for higher geocoding accuracy.

However, it's common for Excel or spreadsheet data to have merged details like street names and house numbers, or postal codes and cities in the same column. How do you handle this when preparing the file for geocoding?

Solution

During the import process, if your data combines multiple address components in one column (such as city and postal code), you can assign the same column to different fields. For example, if a column contains both city and postal code, you can select this column for both the postal code and city fields.

This approach often resolves the issue. If not, it's advisable to further split these columns into separate ones for accurate geocoding.

How to split data into multiple columns?

There are several ways to split columns in Excel or Google Spreadsheets. Here are a few solutions.

Text to Columns

• Excel: In Excel, you can use the "Text to Columns" feature under the "Data" tab. This tool allows you to split text based on delimiters such as commas, spaces, or custom characters. So if you have a cell where city, province and postal code are combined, e.g. HALIFAX, NS, B3J 2B3. You can use this feature and split the column based on a comma.
• Google Spreadsheets: In Google Spreadsheets, you have a similar solution. This feature is called "Split Text to Columns" under de "Data" tab. Most of the times the system will automically detect the right delimiter, but you can also select the delimiter yourself and even specify your own custom delimiter.

Split Functions

• Excel: In Excel, you can also use functions like LEFT, RIGHT and MID to extract specific amounts of text from the beginning, end or middle of a text string.
• LEFT(): Extracts characters from the start of a text string.
• RIGHT(): Retrieves characters from the end of a text string.
• MID(): Extracts characters from the middle of a text string.
• Google Spreadsheets: In here you can use the same function, but there is an additional function in Google Spreadsheets called SPLIT. The syntax is SPLIT(text, delimiter), where text is the original text string, and delimiter is the character or symbol used to separate the elements.