Formating Address to Multple Fields

[Sam]

I have data that needs to be split based on the following information:

Original Field New Field
1 New Field 2
111 SMITH ROAD UNIT 401 11 SMITH ROAD 401
123 JOHNS ROAD UNIT#2S 123 JOHNS ROAD 2S
11199 ROGERS RD UNIT A 11199 ROGERS RD A

Any help with a formula(s) would be greatly appreciated.

Thanks
Samantha

 

[fredg]

I have data that needs to be split based on the following information:

Original Field New Field
1 New Field 2
111 SMITH ROAD UNIT 401 11 SMITH ROAD 401
123 JOHNS ROAD UNIT#2S 123 JOHNS ROAD 2S
11199 ROGERS RD UNIT A 11199 ROGERS RD A

Any help with a formula(s) would be greatly appreciated.

Thanks
Samantha

Permanently change the data?

Run an Update query... (Back up your table first):

Update YourTable Set YourTable.[NewField] =
Mid([OldField],InStr([OldField],"Unit")+6),[OldField] =
Left([OldField],InStr([OldField],"Unit")-2)

Should work, unless an address is on "United Nations Blvd", or
something like that.

 

[Sam]

Is UNIT always in the field?  If so, to parse out the UNIT number:

mid(table.field,InStr(table.field,"UNIT"+1)))

For the street:

left(table.field,InStr(table.field,"UNIT"-1)

Bonniehttp://www.dataplus-svc.com

A combination of both of those worked, thanks! I need to take it one
setp furthur, for those address that do not have unit numbers i need
to have those street be in with streets I just split out to in one new
field and the units by themselves.

using the same examples but adding more I nedd the following to
happen.
111 SMITH ROAD UNIT 401 11 SMITH ROAD 401
123 JOHNS ROAD UNIT#2S 123 JOHNS ROAD 2S
11199 ROGERS RD UNIT A 11199 ROGERS RD A
145 JANES AVE 145 JANES AVE
513 MARY ROAD 513 MARY ROAD


Right now i am getting
111 SMITH ROAD UNIT 401 11 SMITH ROAD 401
123 JOHNS ROAD UNIT#2S 123 JOHNS ROAD 2S
11199 ROGERS RD UNIT A 11199 ROGERS RD A
145 JANES AVE #Error 145
JANES AVE
513 MARY ROAD #Error 513
MARY ROAD
Thanks again for the help!!!

 

[John Spencer]

Are you permanently changing the data with an update query?

If so, then you can add a where clause to the update query

WHERE Table.Field like "* Unit*"

Which says to perform the update only on field that have a string that
starts with a space followed by the letter "UNIT", followed by anything.

If you are just displaying the data you can test the value in the field
for the presence of " Unit" or you can append "Unit" to the end of the
string in the instr to get the values.

Left(Table.Field,Instr(Table.Field & "Unit","Unit")-1)

Mid(Table.Field,Instr(Table.Field & "Unit","Unit")+4)

Optionally, testing before calculating you could use an expression like:
IIF(Table.Field Like "*UNIT*",
Left(Table.Field,Instr(Table.Field,"Unit")-1), Table.Field)

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