Extracting specific parts of an address

I’m using the new google integration to auto complete addresses then sending them over to shipstation via webhook. However, shipstation requires individual address fields such as street, city, state, zip, etc…

Is there any way I can extract the required parts of this single complete address column into individual columns? I tried the math column lol [state(address)] :frowning:

Is the address in a reliably consistent format? Do you have some fake example addresses that follow the format you currently have? Include any variations (street names vs numbers, addition directional indicators such as 12th St N, any with apartment numbers, etc)

Also, does the street include house number and apt/suite number, or are all of those parts separated out as well?

I’m playing around with some results from ChatGPT, but it can largely depend on the format of the address.

2 Likes

The addresses are standard:

123 Street Name Apartment X, City Name, State 55555, USA

Some include a unit/apt #, some follow the example you gave.

Do you always have the country at the end? I wasn’t accounting for that.

The ChatGPT solution wasn’t panning out to my liking, so I started from scratch. This is what I have so far. You can place it into a javascript column and pass the address into the p1 parameter. I’m sure it needs several adjustments, especially if the country is part of the address. Also not really sure how it will handle international addresses.

So the idea is that it will take in an address, split it into individual parts, and then return the result in a pipe delimited format. Then you can take that result and use a split text column to convert it into an array. Then use single value columns to pull out each piece into individual columns.

Let me know what kinds of addresses aren’t working and I’ll try to make adjustments.

try {

// Split Address into two parts
let address = p1.split(',')[0];
let cityStateZip = p1.split(/,(.*)/s)[1];


// Split address into House and StreetUnit
let houseNumber = address.split(' ')[0].trim();
let streetNameUnitNumber = address.substring(address.split(' ')[0].length).trim();


// Split StreetUnit into Street and Unit
let unitIndex = '';
unitIndex = streetNameUnitNumber.toUpperCase().indexOf(' APT');
if (unitIndex<0) {unitIndex = streetNameUnitNumber.toUpperCase().indexOf(' APARTMENT');}
if (unitIndex<0) {unitIndex = streetNameUnitNumber.toUpperCase().indexOf(' SUITE');}

let streetName = '';
let unitNumber = '';
if (unitIndex>0) {
streetName = streetNameUnitNumber.substring(0, unitIndex).trim();
unitNumber = streetNameUnitNumber.substring(unitIndex+1).trim();
} else {
streetName = streetNameUnitNumber.trim() ;
unitNumber = '';
}


// Split cityStateZip
let city = cityStateZip.split(',')[0].trim();
let state = cityStateZip.split(',')[1].trim();
let zipCode = cityStateZip.split(' ')[cityStateZip.split(' ').length-1].trim();



// Joining the extracted parts in a pipe delimited format
let parts = [houseNumber, streetName, unitNumber, city, state, zipCode];
let splitAddress = parts.join('|').replace(',', '');

return splitAddress;

}
catch(error){
}

Another option would be to output the result as JSON, and the each piece of the address can be parsed from the JSON. Just depends on the desired output.

1 Like