Split Address Data

[mboe]

I need to standardize some address fields. I am having a problem with splitting number and letters in a address field. For example:

2400N Main Street

trying to switch to

2400 N Main Street

I can't figure out how to update this in VBA. I tried a update query and can get the criteria to work but not the update. Not sure how the "Update To" field should be formated but I would rather do it in code.

criteria: Like "*" & "#N " & "*"

Here is the VBA code I am using so far in case someone is working on similar issues. Once I get the fields as standardized as possible I can look for duplicates. I know this may never be perfect but I want to get as close as possible.

I have tried this forum and the internet and just can't figure this out. Hopefully someone can help. I can't imagine I am the first person with this issue.

Public Function Clean(ByVal strfield As String) As String
If strfield <> "" Then
strfield = Replace(strfield, ".", "")
strfield = Replace(strfield, ",", "")
strfield = Replace(strfield, "Drive", "DR")
strfield = Replace(strfield, "Road", "RD")
strfield = Replace(strfield, "Avenue", "Ave")
strfield = Replace(strfield, "Street", "ST")
strfield = Replace(strfield, " Ste ", " Suite ")
strfield = Replace(strfield, " P O Box", " PO Box")
strfield = Replace(strfield, " PO B ", " PO Box ")
strfield = Replace(strfield, " POBox ", " PO Box ")
strfield = Replace(strfield, " Box ", " PO Box ")
strfield = Replace(strfield, " PO PO ", " PO ")
strfield = Replace(strfield, "North", "N")
strfield = Replace(strfield, "South", "S")
strfield = Replace(strfield, "East", "E")
strfield = Replace(strfield, "West", "W")
strfield = Replace(strfield, "Lane", "LN")
strfield = Replace(strfield, "Court", "CT")
strfield = Replace(strfield, "County", "CO")
strfield = Replace(strfield, " apt #", " Apt ") 'capture "apt #2", remove the # sign
strfield = Replace(strfield, " Apt ", " # ") 'Replace Apt with # sign


strfield = Replace(strfield, " ", " ") 'Remove 10 Spaces
strfield = Replace(strfield, " ", " ") 'Remove 9 Spaces
strfield = Replace(strfield, " ", " ") 'Remove 8 Spaces
strfield = Replace(strfield, " ", " ") 'Remove 7 Spaces
strfield = Replace(strfield, " ", " ") 'Remove 6 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 5 spaces
strfield = Replace(strfield, " ", " ") ' Remove 4 spaces
strfield = Replace(strfield, " ", " ") ' Remove 3 spaces
strfield = Replace(strfield, " ", " ") ' Remove 2 spaces

strfield = Trim(strfield) 'Trim leading and trailing spaces
Clean = strfield
Else
Clean = ""
End If

End Function

 

[Uncle Gizmo]

No you're not the first person !

I would suggest you search this forum for "parsing" I don't think it's quite what you want because parsing means separating data into separate fields, I don't think you want to do that. However the code may be useful for what you want to do.