separating address from postcode

  • Thread starter Thread starter lbasha
  • Start date Start date
L

lbasha

i am trying to separate an address that looks like this:
'C-15-6 Idaman Sutera Condo Medan Idaman 53100 kuala lumpur'
into:
'C-15-6 Idaman Sutera Condo' - (column 1)
'Medan Idaman' -(column 2)
'53100' - (column 3)
'kuala lumpur' - (column 4
 
Hi

You will find this very difficult unless there is a standard way of deciding
where each line of the address starts and finishes, unless each section of
the address takes up the same number of characters. Is the sample you posted
exactly as you have it? Are there no commas separating each bit?
 
Good luck, because I don't think you are going to find the
answer on either of our sites. Unless everything has the
same number of words or is actually lined up by columns
if viewed in a fixed character font.

Certainly you would have to provide more data to see
what you really have, but I expect that you have street
names and town names that could be single or multiple
words.

Possibly if you examined the data more carefully there might
be TAB characters, Char(09), between fields, possibly
double spaces (Char(09). Certainly nothing like that came
through in the posting. Everything that looked like a space
was a space.

It look like you have a "zip code" equivalent for column 3
as a number, but how would you split between column 1
and column 2.

The idea is to start with good data, not have to process garbage.
If the data came from HTML you can paste it into Excel 97 and up.

If the data came from a mainframe, you can probably get them
to provide it in a fixed format or as a comma separated values.

If you have a words comparable to "Road", "Street", "Avenue"
at the end that can be used as separators that would help as
you would know your own data. You could replace them with the
likes of "Road, ", "Street, ", "Avenue, "
 
Thanks to all,
The only way i found not to mes up the data is by:
-- Copy column + paste in word
-- separate the addresses by an '*'(for example)
-- convert table to text, by chosing paragraph option
-- convert text to table, by chosing others & selecting '*' an
selecting the number of columns
-- copy it back to excel.
This was the best i could do in order not to change the lis
arrangemen
 
Where are you getting the data from.
If you can paste it into Word, I'd think you'd be able to paste it into
Excel. From the original question it looked like you had
a flat file, when you say you paste into Word then I'd think
that was not the case.

If this is from a PDF file then see
http://www.mvps.org/dmcritchie/excel/pdf.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Back
Top