Extrapolate Subunit Info out from Address1 Field?

[aellistechsupport]

Example:
Address1 contains the FULL address like this: 1413 PENNSYLVANIA AVE W APT 1



Would need the above split into 3 columns as below:-

Column 1 = Address1
1413 PENNSYLVANIA AVE W

Column 2 = SubUnitType (sometimes it's "#" and would need that to be converted to say "Unit")
APT

Column 3 = SubUnitNumber (sometimes it's a letter)
1

See attached. The table named "CHANGES_RECORDS". The field is "NEW_ADDRESS1".
Need to create a query that takes the 1st column and the NEW_ fields where the NEW_ADDRESS1 field is where the extrapolation is occurring and in the query produce two additional fields from that with SubUnitType and SubUnitNumber

This will need to be ran monthly, each time a mailing is done and the results returned from the mailing house.NCOA.zip

[Micron]

I looked at your data and see no hope of accomplishing what you want. There might have been a chance of making it happen with string functions if there was a consistent pattern, but there are a lot of entries that bear no resemblance to the example you provided, such as PO BOX 374. This looks nothing like the example you posted, yet it is in the same field.
Perhaps there is a Merlin lurking somewhere in this forum! Good luck.

[aellistechsupport]

NOTE: the PO Box address would remain as is in Address1 column

Right, thought the InStr could work but with the variables, VBA perhaps. I did find a "regular expression" VBA RegExc however, not being that familiar with it, I don't how to apply it.

https://blog.udemy.com/VBA-regex/

[Micron]

I've never used RegEx and don't intend to start now .
Not sure it would help you anyway if your data has no regular pattern as I said. However, maybe that's not the case as I'm not sure now where the string to be parsed is coming from. You say Need to create a query that takes the 1st column but the first column is "Member", which looks nothing like the address you posted. So does this first column reside in some other table, and the field I said has no pattern is the result of a previous try?

This is also one of those cases that gets me scratching my head, wanting to ask why isn't this a one time fix instead of monthly, and what's wrong with the entire address being in one field? The answers may lead to other suggestions, such as, can you have a table of identifiers that can be used to pull out matching strings or at least provide characters to use as section breaks? E.g. a table with Unit, #, Apt, etc. might help. Still, I have to wonder what's wrong with a one line address, especially when it doesn't contain a city, state or postal code.

[aellistechsupport]

1st column in the example posted, as referenced as Address1, not the 1st column of the table.

It's the way the database stores the SubUnit information.
So having to write back to the tables in the database is why I need to find a way to extrapolate the SubUnit information from the NEW_ADDRESS1 column/field in from the CHANGED_RECORDS table.

There's a table with the SubUnit types, which are just:
1. Apt
2. Unit
3. Ste
4. Lot

The "#" that has been returned, I would like it to get translated as "Unit" so it matches one of the SubUnit types.

Of course when a mailing list is pulled or whatever other address list all of the related tables are linked to pull the SubUnit info and is set as a Line 2 item on labels, etc ...
Sometimes, like you've mentioned, and as the mailing house produced back, all in just one line, into NEW_ADDRESS1.
Though that's not the standard way USPS prefers the address info to be placed.

Anyway, went off tangent there.

Issue has been defined. Hopefully more clear now, and looking for assistance on how to do this ...

[Micron]

Well, without some indication of a pattern or consistency, I see no resolution that would enable you to parse the string into chunks. You're saying sometimes APT will be that, sometimes UNIT and sometimes # (and who knows what else). Unless there is a pattern, I don't know of anything that would handle this. The only positive thing I can say is that you can do a find/replace on a table to change # to UNIT. Maybe someone else has some magic up their sleeve.

[aellistechsupport]

The said "pattern" are, as specified:
1. Apt
2. Unit
3. Ste
4. Lot
5. # - which should be converted to Unit

Hopefully. Is there a way to "bump" this?

[Micron]

What you've shown is not a pattern that anyone can use to extract words from several records.
This is a pattern
1413 PENNSYLVANIA AVE W APT 1
2222 Oregon AVE S Unit 1
3333 Boston Blvd E
1414 Maine St
Everything has a delimiter and a set order. I could code to pick out the number and/or street name for any of these records. Beyond that is questionable.
Bumping is what you do when nobody responds. I hear you and will bow out.
Good luck.

[aellistechsupport]

Anyone know how to modify the .Pattern portion in this code from thedbguy to extract out the "subunittype": APT, UNIT, LOT, # as UNIT in one column and the "subunitno": 1, E, etc ... from the end of the Address field??

Public Function ExtractData(strData As String, _
    Optional strDelim As String = ";") As String
'http://accessmvp.com/thedbguy
'2/9/2016
'Extracts data using regular expressions

Dim regEx As Object
Dim regExMatch As Object
Dim var As Variant
Dim strMatches As String

Set regEx = CreateObject("VBScript.RegExp")

With regEx
    .Global = True
    .IgnoreCase = True
    .Pattern = "\[(\w+)\]"
    Set regExMatch = .Execute(strData)
    For Each var In regExMatch
        strMatches = strMatches & strDelim & .Replace(var, "$1")
    Next
End With

ExtractData = Mid(strMatches, Len(strDelim) + 1)

Set regEx = Nothing
Set regExMatch = Nothing

End Function

[Perceptus]

A Regex pattern is just shorthand for parsing the data yourself. I wouldnt recommend using it if you dont know how. That being said there are plenty of white papers on pattern matching, and a few tools to generate patterns.

You want to match "SUBUNITTYPE" text, then a colon following with any character between. then match the data to delimit the end of the token.
http://www.w3schools.com/jsref/jsref_obj_regexp.asp

https://regex101.com/

Like buying a data parser written for use in a language you dont know. Sure Russian looks cool, but I wouldnt want to use it.

[aellistechsupport]

Sorry, not following.
However, yes, want to extract out the "SubUnitType" from Address1 as a column.
Then the "SubUnitNo" following the SubUnitType from Address as another column.

Where the "SubUnitType" in Address1 consists of, and always at the end:
1. APT
2. UNIT
3. LOT
4. # - convert to UNIT

[Perceptus]

untested ParseIT("1413 PENNSYLVANIA AVE W APT 1",Line1,Line2,Line3) returns
lines like you want. "I think"

Function ParseIT(Byval strData,byref Line1, Byref Line2, Byref Line3)
Dim strSplit() as string
strSplit = split(strReverse(strData)," ",3)
Line1 = strReverse(strsplit(2)))
Line2 = strReverse(strSplit(1))
Line3 = strReverse(strsplit(0))
end Function

[aellistechsupport]

Thanks.
Will try that.

So far this is what I have:
I sort of have something with:

IIf(InStr(1,[NEW_ADDRESS1],"Apt")>0,Mid$([NEW_ADDRESS1],InStr(1,[NEW_ADDRESS1],"Apt")-1),[NEW_ADDRESS1])

But the not sure how to also add:
"Unit", "Lot", "#"

And to not display the address:

Add1	SubUnit
11 Nathan Pratt Dr Unit 3	11 NATHAN PRATT DR UNIT 3
250 Blue Ridge Rd	250 BLUE RIDGE RD
580 Washington St Unit 401	580 WASHINGTON ST UNIT 401
47 Evergreen Rd	47 EVERGREEN RD
26 Avon St Apt 1	APT 1
175 Centre St # 403	175 CENTRE ST # 403
3529 Surfwood Rd	3529 SURFWOOD RD
36 College Ave Apt 44	APT 44
61 Roxana St	61 ROXANA ST
9 Remsen Ave	9 REMSEN AVE
12 Benton St Apt 1	APT 1

Then I can do another column to extract the numeric part after Apt, Unit, Lot, #
(though if the # can be converted to say Unit, that would be ideal)

[aellistechsupport]

can't get your function to work.
Red through

Line1 = strReverse(strsplit(2)))

[Perceptus]

looks like one too many parenthesis.
also the pattern to match the unit type is
(?:APT)|(?:=STE)|(?:STE)|(?:UNIT)

I put that together in about 3 minutes just using the 2nd link i provided with the information you gave.