Address all in one field

  • Thread starter Thread starter kenneth_lad
  • Start date Start date
K

kenneth_lad

hi,

i've inherited a database at work to sort out and hove noticed that people
have been keying a whole address into one cell (it's been input via a
form). this obviously isn't very good.

the question i have is how can i split the address cell content into
columns (text-to-columns) as the address itself has no delimiter (such as a
comma that i can use to split it), it only has only line breaks. e.g:

101 Rose House
Rose Hill

i'm sure there is some way to do this but i don't know myself :)

thanks to anyone who can help me.
 
hi,

i've inherited a database at work to sort out and hove noticed that people
have been keying a whole address into one cell (it's been input via a
form). this obviously isn't very good.

the question i have is how can i split the address cell content into
columns (text-to-columns) as the address itself has no delimiter (such as a
comma that i can use to split it), it only has only line breaks. e.g:

101 Rose House
Rose Hill

i'm sure there is some way to do this but i don't know myself :)

thanks to anyone who can help me.
Click to expand...

Use the Line 'break' as delimiter:

StreetAddress:Left([FullAddress],InStr([FullAddress],chr(13) &
chr(10))-1)
City:Mid([FullAddress],InStr([FullAddress],chr(13) & chr(10))+2)
 
the question i have is how can i split the address cell content into
columns (text-to-columns) as the address itself has no delimiter (such as a
comma that i can use to split it), it only has only line breaks. e.g:
Click to expand...

Actually the field values probably do have delimiters, they're just not
obvious to the naked eye when viewing the data in Access.

Try using the InStr() to search for one of these: VbCr, VbCrLf, VbLf. Those
are VBA constants which represents carriage return and line feed - which are
actual Ascii characters. Once you know the exact character(s) which serve as
a delimiter, you can use Spit().
 
One way to do it would be to export the table to Excel and
use the 'text to columns' feature that it has. I have used
this feature many times. You can tell it where to parse be
it a space or something else.

Hope this helps,
Bandit
 
Back
Top