Search code examples
regexstringgoogle-sheetsgoogle-sheets-formulaarray-formulas

Is there a method for dividing an Address string into 3 separate strings using regex

I am currently working on a project that requires me to divide an address into its street number, its street name, and if it has a suite, into its suite name. EX: 1360 WHITE OAK RD STE F -----> 1360 | White Oak RD | STE F

I am currently using google sheet and using the =regexextract() functionality that uses Regex to parse the string into different columns. This is how I am currently dividing the number and the street (given the full address is in column B.

=ArrayFormula(REGEXEXTRACT(B1:B,"[0-9]*")) ---->gets the number EX:(1360)

=ArrayFormula(REGEXEXTRACT(B1:B," [a-zA-Z0-9 ]+")) ---->gets the street address including the suite number with a white space at the begining EX:( WHITE OAK RD STE F)

The question I am struggling with is how do I remove the white space from the 2nd formula and also prevent it from getting the suite text (which always starts with STE). Lastly what would be a formula for grabbing the suite text and number.

Thanks and I appreciate any help you can give!

Solution

  • For Google Sheets you could use the following 3 formulas:

    =REGEXEXTRACT(B1,"^[0-9]*")
=REGEXREPLACE(B1,"^[0-9\s]*|\s*STE.*$", "")
=REGEXEXTRACT(B1,"STE.*$")

    I would have used lookbehinds but they are not universally supported in all browsers (yet).

    I'm not a Google Sheets expert so I've opted to remove ArrayFormula and replace the B1:B with just B1 since they seemed superfluous.