If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query numeric values from text
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? -- Mary |
#2
|
|||
|
|||
query numeric values from text
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 "Mary G" wrote in message news 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? -- Mary |
#3
|
|||
|
|||
query numeric values from text
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 wrote: 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? |
#4
|
|||
|
|||
query numeric values from text
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. -- Mary "Jeff Boyce" wrote: 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 "Mary G" wrote in message news 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? -- Mary |
#5
|
|||
|
|||
query numeric values from text
Mary
So, you were focusing on a "how" when the "what" is "look for duplicates"? Regards Jeff Boyce Microsoft Office/Access MVP "Mary G" wrote in message ... 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. -- Mary "Jeff Boyce" wrote: 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 "Mary G" wrote in message news 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? -- Mary |
Thread Tools | |
Display Modes | |
|
|