query numeric values from text

[Mary G]

Hello,
I'm not finding an exact match to my situation. I have received an export
file with the address (street address, city, state, zip) all contained in one
field. There aren't any delimiters. I would like to pull the numeric values
only (with a "," delimiter, such as:

Data: 123 North 4th Street New York NY 12345
I would like: 123,4,12345

Any ideas?

 

[Jeff Boyce]

Mary

We aren't there. We don't know your intended use of "123,4,12345".

The first thing that popped into my mind when reading your description is
"what happens if the address is spelled:
123 N Fourth St Apt 3 New York NY 12345

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

The only way I can see to handle that is to use a custom VBA function that
steps through the string and pulls out the information. Also there could be
problems with data string that looks like
123 1/2 North 4th Street New York NY 12345-0122
123,1,2,4,12345,0122

Or if your data is limited to 5 digit zip codes, how about
123 1/2 North 4th Street Apt 2A New York NY 12345
123,1,2,4,2,12345

Not a trivial exercise to write this.

Ignoring exceptions the following may give you some ideas on getting the
results you want.

Public Function fGetNumberString(strIN)
Dim vAr As Variant
Dim strResult As Variant
Dim i As Long

If Len(strIN & "") = 0 Then
strResult = strIN
Else
vAr = Split(strIN, " ")
For i = LBound(vAr) To UBound(vAr)
If Val(vAr(i)) <> 0 Then
strResult = strResult & "," & Val(vAr(i))
End If
Next i
End If

If Len(strResult) = 0 Then
fGetNumberString = Null
Else
fGetNumberString = Mid(strResult, 2)
End If

End Function

Good Luck

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Mary G]

Good point on spelling a portion of the numeric address. I'm trying to see if
it is feasible to get a rough download to identify large groups of potential
duplicate addresses. The purpose is to identify any high risk activity for
new account opening. I can see that this may be more that we anticipated!!
Thanks for your ideas. Let me know if you think of anything else that would
be helpful.

 

[Jeff Boyce]

Mary

So, you were focusing on a "how" when the "what" is "look for duplicates"?

Regards

Jeff Boyce
Microsoft Office/Access MVP