David,

Sorry for the delay. Hope you are still checking this post.

If it is a carriage return, then try these two formulas in two columns next
to the one containing the address info:

=MID(A1,1,FIND(CHAR(10),A1)-1)
=MID(A1,FIND(CHAR(10),A1)+1,LEN(A1)-FIND(CHAR(10),A1))

These will return the text before (first formula) and after (second formula)
the carriage return in the cell.

The Char(10) in the formulas represents the code for a carriage return.

If the above formulas don't work, it is possible that the "carriage return"
in your report is a different character.

To find the right character use the formula:

=code(mid(a1,8,1))

Where "8" is the character position of the carriage return in the cell. Just
count up all the characters (including spaces) before the the carriage return
and add 1.

Then substitute whatever number you find for "10".

Does that work?




"DGMarsh" wrote:

HI Dominic,

The data that I'm importing is actually a report that's generated by a
system that I can't control (otherwise I'd send the data out in an acceptable
format). By the time the excel import occurs the address number and street
are in the same cell as the city, state and zip; it wraps, because there's
the carriage return causing that. I would have thought that the delimit
functions of the import filter would include carriage return as a possible
delimiter, but it doesn't appear to.

Does this explain my need a little better?

Thanks for the speedy reply!
David Marsh - Seattle

"Dominic LeVasseur" wrote:

Hi DGMarsh,

If you can't get the import filter to work for you, you could separate them
after they are in Excel.

First though, what happens when you import the data? Is the street in the
same cell as the city/state/zip? Or are they in different cells/rows? How
about in the import wizard - same line or different line?



"DGMarsh" wrote:

can I extract a portion of a field based on a carraige return in the data.
In other words, I have an extract from a program that gives me the address as
two lines seperated by a carraige return. i.e.
1234 Anystreet
<carraige return
Anytown, ST ZIPCD

I want to be able to get the city into a column of its own, but the import
filters don't seem to be able to make the distinction to make the seperation
of fields.

HELP!!!