Address split into separate fields

Without a separator between address and city I think you would have a very difficult time figuring out programmatically when an address ends or a city begins.

If this format is what you will be working with, then the only other way is create a table of city names that you can check against.

If this is for a business then there are companies that can provide you with a csv that you can import. Or even better maybe through API you can get the data. I'm guessing that this can be thousand, tens of thousands of records.

A quick Google search brings up 3 year old data that is free for use.

It was an interesting thought process on how to separate the string into the various parts. I've attached a DB that show one way of doing it.

Go to the Page1 page and you can see how it works. I've created a small States/Cities table to run this test.

The firstStep field is where the raw data sits, so you can plug in data here.

The zipCode field uses extractx() to get the zip code. It looks for whole "words" before the last space so it will return 5 digit zips or Zip+4.

The state field uses extractx() again to find the second to last "word".

In the City field, I first get all Cities records that have the same zip code. Then I use a for loop to go through the records in cityRecs to see if any of the names are in firstStep. I add a comma to the end of the city name to make sure we don't find city names in addresses.

The Address field then can use the City data and extractx() to get everything before the data in City.

The one issue I see is that the code for the City can be really slow once you get all of your city names, since we use a select and for loop. I don't know what your work flow will be but I would put the code into a button that does this work then puts the separated data into text/number fields so you don't make Ninox run the code all the time.

You might have to make the following change to the Address field:

let regexCode := "(.*?)" + City + ",";
extractx(firstStep, regexCode, "i", "$1")

I found this address:

73455 Twentynine Palms Highway Twentynine Palms, CA 92277

and if you don't look for the City Name with the comma at the end it just returns 73455.

So I tried using U Man Genius' Ninox ChatGPT for this and this is what it recommended:

function getCity(address : text) do
  "Extracting the city name";
  var parts := split(address, ", ");
  var location := parts[0];
  var cityAndDetails := split(location, " ");
  var statePart := parts[1];
  var stateWords := split(statePart, " ");
  var stateCode := first(stateWords);
  var city := replace(location, stateCode, "");
  city
end;

I copied it into Ninox and got errors on lines 5,7,9. So I removed the function part and started troubleshooting. It looks like even thought parts is an array you can't use the square brackets to get values in the array. The variable location returns an empty array.  If you switch the code to:

 var location := item(parts,0);

then the editor stops throwing errors for the location variable.

So you have to do the same thing for line 6 variable statePart.

In the end the code doesn't isolate the City name. If I enter:

24 Waverly Pl New York, NY 10003

I get:

24 Waverly Pl New York

Oh, well. It tried. It was fun to walk it through the coding. ThanksJacques TUR and Jan for this.