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”?