maybe add a function
Public Function getZipCode(ByVal strparam As Variant) As String
Dim strValue As String, i As Integer
strparam = strparam & vbNullString
If Len(strparam) > 0 Then
With CreateObject("VBScript.RegExp")
.Global = True
.IgnoreCase = True
.Pattern = "[a-zA-Z\ ]*"
strValue = Trim(.Replace(strparam, ""))
i = InStr(1, strValue, "-")
If i > 0 Then
strValue = Left(strValue, i - 1)
End If
.Pattern = "\D*"
strValue = Trim(.Replace(strValue, ""))
End With
getZipCode = strValue
End If
End Function
''''
on your worksheet
=getZipCode(C3)
My office uses a commercial software package that is the worst designed SQL server in the world.
I'm making a report to group by several fields, right now the zipcode. I need to extract the zipcode out of the field [JobAddressLine3]. The problem is, the company threw in the city, state, and zip into one field for example: AURORA CO 80013
There isn't a set number of spaces between the state and the zipcode. Now I can use a simple code like: ZipCode: Right([JobAddressLine3],5). This works fine, except, when the address contains a zip +6 (80111-540577) then my code returns 40577 instead of the 80111. There are a ton of other variables that I can't control such as punctuation (Denver, CO vs Denver CO for example).
Any ideas on how to strip the zip out taking into account the zip + 6 issue?
I've attached a spreadsheet with a few examples, I've changed the addresses to 5555 for privacy issues.
Arnelito G. Puzon
_._,_._,_
Groups.io Links:
You receive all messages sent to this group.
View/Reply Online (#115748) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]
_._,_._,_
Tidak ada komentar:
Posting Komentar