I have complete addresses including hard carriage returnss in my original
data. (That is, I have three lines in each cell.) How do I make the CR's a
delimiter for text-to-column conversion?

One option is to substitute the carriage return for other symbol. You can
create an additional column with a formula like:
=SUBSTITUTE(A1,CHAR(10),"|")
And perform the text to columns on the results, using the | as separator.

Hope this helps,
Miguel.

"CraigR53" wrote:

I have complete addresses including hard carriage returnss in my original
data. (That is, I have three lines in each cell.) How do I make the CR's a
delimiter for text-to-column conversion?

Thanks, Miguel. That got rid of the CR, but there is still a square
immediately prior to the place where the CR was. I throught it was just a
placeholder for the CR, but it must be something else. How do I get rid of
that?


"Miguel Zapico" wrote:

One option is to substitute the carriage return for other symbol. You can
create an additional column with a formula like:
=SUBSTITUTE(A1,CHAR(10),"|")
And perform the text to columns on the results, using the | as separator.

Hope this helps,
Miguel.

"CraigR53" wrote:

I have complete addresses including hard carriage returnss in my original
data. (That is, I have three lines in each cell.) How do I make the CR's a
delimiter for text-to-column conversion?

You can use the function CODE to check what that square is, and then
substitute it again, this time with the empty character. To use code, I
would combine it with the MID function, to aim where the character is.
For example, if you have 2 characters before the square, use the formula:
=CODE(MID(A1,3,1))
This will return a number, then you can use that number in the CHAR part of
the formula:
=SUBSTITUTE(A1,CHAR(XX),"")

Hope this helps,
Miguel.

"CraigR53" wrote:

Thanks, Miguel. That got rid of the CR, but there is still a square
immediately prior to the place where the CR was. I throught it was just a
placeholder for the CR, but it must be something else. How do I get rid of
that?


"Miguel Zapico" wrote:

One option is to substitute the carriage return for other symbol. You can
create an additional column with a formula like:
=SUBSTITUTE(A1,CHAR(10),"|")
And perform the text to columns on the results, using the | as separator.

Hope this helps,
Miguel.

"CraigR53" wrote:

I have complete addresses including hard carriage returnss in my original
data. (That is, I have three lines in each cell.) How do I make the CR's a
delimiter for text-to-column conversion?

One more method is to use ALT + 0010 from the numpad and Text to Columns

Delimited byOther ALT + 0010 Or CTRL + j


Gord Dibben MS Excel MVP

On Mon, 14 Aug 2006 12:10:02 -0700, Miguel Zapico
wrote:

One option is to substitute the carriage return for other symbol. You can
create an additional column with a formula like:
=SUBSTITUTE(A1,CHAR(10),"|")
And perform the text to columns on the results, using the | as separator.

Hope this helps,
Miguel.

"CraigR53" wrote:

I have complete addresses including hard carriage returnss in my original
data. (That is, I have three lines in each cell.) How do I make the CR's a
delimiter for text-to-column conversion?

In the "delimited" section, "check" other and enter "0010", using the
number pad while holding down the Alt key
"CraigR53" wrote in message
...
I have complete addresses including hard carriage returnss in my original
data. (That is, I have three lines in each cell.) How do I make the CR's
a
delimiter for text-to-column conversion?

I tried the alt 0010 suggested and everything to the right of the carriage
return disappears. Not really what I was hoping for.
--
I know enuff to be dangerous.


"Paul Dusterhoft" wrote:

In the "delimited" section, "check" other and enter "0010", using the
number pad while holding down the Alt key
"CraigR53" wrote in message
...
I have complete addresses including hard carriage returnss in my original
data. (That is, I have three lines in each cell.) How do I make the CR's
a
delimiter for text-to-column conversion?

Maybe you had "do not import column" checked for the column right of the CR?


Gord Dibben MS Excel MVP

On Mon, 12 Feb 2007 11:14:01 -0800, RealGomer
wrote:

I tried the alt 0010 suggested and everything to the right of the carriage
return disappears. Not really what I was hoping for.

Nope. When I open the text to columns window and choose delimited, everything
to the right of the symbol disappears.
--
I know enuff to be dangerous.


"Gord Dibben" wrote:

Maybe you had "do not import column" checked for the column right of the CR?


Gord Dibben MS Excel MVP

On Mon, 12 Feb 2007 11:14:01 -0800, RealGomer
wrote:

I tried the alt 0010 suggested and everything to the right of the carriage
return disappears. Not really what I was hoping for.

It will come back when you hit "Next".


Gord

On Mon, 12 Feb 2007 14:15:02 -0800, RealGomer
wrote:

Nope. When I open the text to columns window and choose delimited, everything
to the right of the symbol disappears.