N
NumberDocc
I am trying to parse addresses into their basic components (I.E. "123 Main
Street" becomes "123", "Main", "Street") in an Excel Spreadsheet, for later
conversion into a Relational DB.
If H2 = 123 Main Street
To get the Address number (D3): TRIM(MID(H2,1,FIND(" ",H2,1)))
To get the street name (E3):TRIM(MID(H3,LEN(D3)+1,FIND("
",H3,LEN(D3)+1)+LEN(D3)+1))
To get the street type (F3): TRIM(MID(H3,FIND(E3,H3)+LEN(E3),LEN(H3)))
My problem is when the street name is two words (North Main Street). I get
a street name of North and a street type of Main Street. It is also possible
that the street may have a 3 word name.
My first qusetion is how can I do a search starting from the right (RIGHT
function does not do it) to find the last word in the string?
Also, is there a cleaner way to parse the strings than I have done?
Thanks for you help.
Street" becomes "123", "Main", "Street") in an Excel Spreadsheet, for later
conversion into a Relational DB.
If H2 = 123 Main Street
To get the Address number (D3): TRIM(MID(H2,1,FIND(" ",H2,1)))
To get the street name (E3):TRIM(MID(H3,LEN(D3)+1,FIND("
",H3,LEN(D3)+1)+LEN(D3)+1))
To get the street type (F3): TRIM(MID(H3,FIND(E3,H3)+LEN(E3),LEN(H3)))
My problem is when the street name is two words (North Main Street). I get
a street name of North and a street type of Main Street. It is also possible
that the street may have a 3 word name.
My first qusetion is how can I do a search starting from the right (RIGHT
function does not do it) to find the last word in the string?
Also, is there a cleaner way to parse the strings than I have done?
Thanks for you help.