Extract varying lengths of text

I’m trying to extract only the street address (number and name) from a column that contains the full address, including the city, state and zip.

I was able to do this successfully with a different spreadsheet that has commas separating each section of the address. However, this spreadsheet does not have a comma between the street address and the city.

Additionally, some of the street names are made up of two or more words which makes using a character space as a delimiter seem impossible. As an example, if the address is formatted as “1234 Country Park Way Sarasota, FL 34243”, I’m able to individually extract “1234”, “Sarasota”, “FL”, and “34243” successfully. Since there is no comma between “Way” and “Sarasota”, AND there are multiple spaces between “1234” and “Sarasota”, I’m unable to isolate and extract the street name.

Counting characters doesn’t seem to work either because of the many variations in lengths and number of words in each of the addresses.

Is there a way to isolate and extract the street name so the data in the new column is “1234 Country Park Way”, while also extracting a different street name such as “567 Main”?

Hi @Ken_Aldridge - Hmm this is a tough one, especially to handle well in a sustainable way.

Is this data set going to be static? Or will it continue changing?
If it is static, how many total addresses do you have on your list? One way to try to solve for this is to create a finite list of delimiters that will mark the end of a street name like: way, blvd, street, place, main. Obviously the feasibility of this will depend on the size of your data set.

Hi @sachi,

I receive a new set of data each month. There are typically around 1000 addresses. I thought about adding a delimiter (like a comma). If I could do that, it would likely sovle the problem. I went as far as extracing each of the city names so I could group them together. Unfortunately, that approach doesn’t work either because the city names are copied vs. moved and the original data is still intact. Any ideas?


I think I just figured it out. I’m using Magic Cleaning. Will let you know how it goes once I’ve built it out and tested.

1 Like


Magic cleaning did the trick. There were several prerequisite steps needed to isolate the Street Name and City in to a single column. Then, I replaced each city with a space, leaving only the Street Name.

1 Like

@Ken_Aldridge - Glad to hear you were able to figure this out! Hopefully the new data set you receive each month will be similar enough to your current data set to avoid having to update your flow. Let us know how this continues to work for you!

1 Like