Column Splitting - Parsing Out Address

In this example you are going to learn how split columns in you data set. In this sample file we have two kind of records. First one has the address in one column and the second one has it split into city and street.

Source file: addresses.xlsx

For better analysis options it would be better to have it separated in all rows.

1. Upload sample data file into BellaDati.

2. Add transformation script to column City by hovering over column name and clicking Edit transformation script.

   Extracting City Name from the Address

   if (isEmpty(value(2))){
   substringAfter(value(1), ',')
   }
   else return value(2)

   This script is checking whether the column City is empty. In case it is, part of first column (Address) starting after comma is used. In case it is not empty, current value is used.
   
   
   

3. Add transformation script to column Street by hovering over column name and clicking Edit transformation script.

   Extracting Street Name and Number from the Address

   if (isEmpty(value(3))){
   substringBefore(value(1), ',')
   }
   else return value(3)

   This script is checking whether the column Street is empty. In case it is, part of first column (Address) ending before comma is used. In case it is not empty, current value is used.
   
   
   

4. Exclude column Adress from import
   
   
   
5. Click on Continue and Import data.
6. The result should look like this: