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

@Jeff_Hager this is a great solution. What if I only want to pull out the City from the address, what edits would I make to this script? I’ve tried editing this myself but my lack of JS skills lead to errors and unintended results. :laughing:

Any input would be greatly appreciated! Thx

If the above code works for you and you just wanted only the City, then you would only need to set ‘parts’ to only grab City and nothing else.

let parts = [city];

Just note that this does heavily rely on consistent address formatting and a certain format at that. It’s not a perfect solution.

In fact it may be better to split from back to front instead of front to back if the only thing you want is City. This would simplify the javascript. The question would be what do your addresses look like. Do they always end in City, State, Zip? Similar commas, spaces, etc.?

2 Likes

Hi, @Jeff_Hager. Yes, I plan on using Radar to auto-populate the address input on the form I’m building and from my testing the formatting seems consistent:

[Address 01] [Address 02], [City], [State] [Zip] [Country]

The edit you gave me works perfectly (of course!) but I’m also considering just using Split Text to convert the address into an array, using Single Value > From end to pull out the city, and then remove the stray comma with a Template column. I think this way would avoid any issues in case Radar spits out anything funky…would you agree?

Why don’t you use a comma as your delimiter?

2 Likes

If you’re using the Radar API, you should be able to extract the City directly with a Query JSON column.

3 Likes

Hey @ThinhDinh. Yeah, that is much cleaner and eliminates an extra column. The goal here is to create some in-app filter parameters. My thought is in case the client wants to filter by state, zip, or country in the future, I can just add additional SV columns to accommodate. Am I overthinking?

Hi @Darren_Murphy. Appreciate the input. I’ve only tested the Radar output using an installation in someone else’s app; I haven’t set up the API yet so don’t know exactly what my options are yet. Does the query workflow you describe consume updates or is like having Radar convert addresses to long and lat?

The client is on a Maker plan, wants address autopopulate — which will already consume a considerable amount of updates — so trying to limit usage wherever I can.

I assume that the address is coming from the API response? Once you have the response, extracting specific parts of it won’t use any additional updates.

I recall that Bob Petitto did a tutorial on address auto-complete shortly after the Radar integration was added. You should check that out.

1 Like

Perfect, I was hoping that was the case. I’ll definitely check out Bob’s video to keep me on track during implementation.

Thx to both you and @ThinhDinh for chiming in on this…much appreciated! :+1:

1 Like

Here is that tutorial:

2 Likes