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.