export address field of Access data into columns in excel

[Guest]

Hello,
I am trying to export data from the address field in Access to Excel
spreadsheet. The data contains the special character that separate the
address lines.
For e.g. data export to address column of Excel (all lines into 1 column):
123 First Street <special character - ||>Block E, Emp Bldg<special character
- ||>New York, NY, 11111

I will like to export the data into excel separating them into different
columns e.g.
|Col 1 |Col 2 |Col 3
|123 First Street |Block E, Emp Bldg |New York, NY, 11111

so that I can rename the column to different address lines.

Appreciate the help given.
Steve

 

[Guest]

In your VB editor, look in Help for the Split Function.
All you need to know is what the special character is. Hopefully, it will
be the same all the time. For example purposes, lets say it is |.

strAddr = "123 First Street | Block E, Emp Bldg|New York, NY, 11111"
varCorrectAddress = Split(strAddr, "|")
Now varCorrectAddress will be an array with 3 elements:
varCorrectAddress(0) will be 123 First Street
varCorrectAddress(1) will be Block E, Emp Bldg
varCorrectAddress(2) will be New York, NY, 11111