ICON
Help Center / Split values in Excel for geocoding /

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.

Split Values in Excel or Google Spreadsheets

Text to Columns

Split Functions

Split functions explained. A few examples.

  • RIGHT() function: When you have a column where both postal code and city are combined, you can take advantage of the fact that postal codes always have the same amount of digits. So when you have cell like this "HALIFAX NS B3J 2B3" where you want to extract the postal code at the end, we have to extract the last 7 characters (spaces included). To do this you can use the following function to extract this postal code: "=RIGHT(CELL, 7)".
  • SPLIT() function: For example, if you have the text "HALIFAX,NS,B3J 2B3" in a cell, you can use SPLIT(CELL, ",") to split this text into three separate cells containing "HALIFAX", "NS", and "B3J 2B3".

Related blog posts

Access the Free Spotzi Explorer

Gain access to our free catalog of maps and dashboards. Find Postal Codes, learn more about global income differences or just get inspired by our community maps. Spotzi Explorer is our free solution for every marketer, researcher, student and beyond. Available as a stand alone solution and part of our price plans.

Get free access to Spotzi Explorer Read more
thumbnail Spotzi Audiences now available via Programmatic OOH

Blog

Spotzi Audiences now available via Programmatic OOH