Address Parser/String Manipulation

[Chaos424]

I need to take a full address that is in (1) field and parse that string into House #, Street Name, Street Type, and Direction and place the new strings into blank fields in the spreadsheet.

 

[VBAjedi]

We need actual data examples, and we need to know what the data "rules" are. For example: there is always a street number and street type, but not always a Direction. Street names may have spaces in them. Etc. etc.



VBAjedi

 

[Chaos424]

The rules:

1.) Street Names will be concatenated (all one word)
2.) An address will always have a Direction N,S,E,W,NE,etc.)

example: "1700 GeorgeMason Boulevard N

 

[PHV]

Take a look at the Split function (office 2000 or above).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fredericofonseca]

On this particular case (based on your rules) you can also do the following.

1- find the first space on the string (A)
2- find the last space on the string (is is either len -1 or len -2, but you can do a minor loop starting on len of field by -1). (B)

so house number is equal mid(str,1,A-1)
streets are mid(str,a+1,b-1)
and you can figure the remaining.

if you also need the street to be "GeorgeMasonBoulevard", then you need to do a loop to remove the spaces between loops. there is no way around this that I know of (Is there??))

But you may have more rules and in the end it may turn out to be more complicate than this.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[mintjulep]

Find length of string.

If char at length - 1 is a space, then you have a N, S, E or W. Take the last char and place it in the appropriate field.

If the char at length - 1 is not a space then you have NE, NW, SE, SW, etc. Take the last two chars and place them in the appropriate field.

Search in turn for Boulevard, Avenue, Street, Lane, Drive, etc. When you get a match you know what you have, and can make the appropirate entry in the appropriate field. While doing this, flag the location of the first letter of the match.

Put everything from string position 1 to the location of the first letter flagged above - 3 into the address field.

If your data all follows the rules, this should get you close.

Sorry, can't help with inserting spaces where needed in the address.

 

[byundt]

Here's a function using the RegExp object from VB Scripting to detect a change from lowercase to upper case. It will then add a space between those words.

Function SplitOnCapitalization(str As String) As String
Dim RgExp As Object
Set RgExp = CreateObject("VBScript.RegExp")
RgExp.Pattern = "([a-z])([A-Z])"
RgExp.Global = True
SplitOnCapitalization = RgExp.Replace(str, "$1 $2")
End Function

Brad