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!!!

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!!!

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!!!

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!!!

Dominic,

Thank you so much! I have not had an opportunity to try this, but I'm sure
that it will either work or that you've put me on the right track. I used to
write code so I'm sure I can tackle it from here. Sometimes I forget about
those old skills when I'm dealing with a product that usually operates at a
higher level; thanks for the refresher!

David Marsh
Seattle

David,

You are very welcome. I hope you get it working well. I have to work with a
lot of imported data as well and it is always interesting massaging it into a
format that Excel can work with.

Another try if you're interested is to highlight the column in question,
then go to Edit:Replace

Type 0010 while holding down the <Alt key in the Find box.

In the Replace box type a character that you can then use Data:Text to
Columns on. Such as a colon.

Have fun!


"DGMarsh" wrote:

Dominic,

Thank you so much! I have not had an opportunity to try this, but I'm sure
that it will either work or that you've put me on the right track. I used to
write code so I'm sure I can tackle it from here. Sometimes I forget about
those old skills when I'm dealing with a product that usually operates at a
higher level; thanks for the refresher!

David Marsh
Seattle