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?

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?

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?

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.

Sorry, Gordo. No dice. Everything to the right gets whacked. Must be a new
feature, like what we have at work. "It's there, you just can't see it."
(Direct quote from tech support)
--
I know enuff to be dangerous.


"Gord Dibben" wrote:

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.

What you describe happening is what *should* happen.

After you check "Other", and then hold down <Alt,
And then type
0010
using the numbers from the *num* keypad,
*not* the numbers under the function keys,
You should see your data separated into columns in the "Preview" window,
JUST AS YOU ENTER THE LAST ZERO,
without any other keystrokes.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RealGomer" wrote in message
...
Sorry, Gordo. No dice. Everything to the right gets whacked. Must be a new
feature, like what we have at work. "It's there, you just can't see it."
(Direct quote from tech support)
--
I know enuff to be dangerous.


"Gord Dibben" wrote:

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.

No go, Joe. Everything to the right of the line feed / carriage return
disappears.
An example:
"BOCA RESTAURANT GROUP LLCDBA BOCA3200 MADISON RDCINCINNATI, OH45209"

Becomes:
BOCA RESTAURANT GROUP LLC

In the original Crystal Reports 7 report, before being export by Crystal,
the entry was:
"BOCA RESTAURANT GROUP LLC
DBA BOCA
3200 MADISON RD
CINCINNATI, OH 45209"

I hope someone can figure out this "feature". I have another few thousand
records in another report that need to be parsed, cleaned, and printed.

--
I know enuff to be dangerous.


"Ragdyer" wrote:

What you describe happening is what *should* happen.

After you check "Other", and then hold down <Alt,
And then type
0010
using the numbers from the *num* keypad,
*not* the numbers under the function keys,
You should see your data separated into columns in the "Preview" window,
JUST AS YOU ENTER THE LAST ZERO,
without any other keystrokes.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RealGomer" wrote in message
...
Sorry, Gordo. No dice. Everything to the right gets whacked. Must be a new
feature, like what we have at work. "It's there, you just can't see it."
(Direct quote from tech support)
--
I know enuff to be dangerous.


"Gord Dibben" wrote:

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.

In your string of text where is the CR(s) located?

If you care to, you could email me a copy of the workbook.

Change the DOT and AT


Gord

On Tue, 13 Feb 2007 13:12:01 -0800, RealGomer
wrote:

No go, Joe. Everything to the right of the line feed / carriage return
disappears.
An example:
"BOCA RESTAURANT GROUP LLCDBA BOCA3200 MADISON RDCINCINNATI, OH45209"

Becomes:
BOCA RESTAURANT GROUP LLC

In the original Crystal Reports 7 report, before being export by Crystal,
the entry was:
"BOCA RESTAURANT GROUP LLC
DBA BOCA
3200 MADISON RD
CINCINNATI, OH 45209"

I hope someone can figure out this "feature". I have another few thousand
records in another report that need to be parsed, cleaned, and printed.

Hey Gord,

Please let us know if that invisible character is *not* 0010.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
In your string of text where is the CR(s) located?

If you care to, you could email me a copy of the workbook.

Change the DOT and AT


Gord

On Tue, 13 Feb 2007 13:12:01 -0800, RealGomer
wrote:

No go, Joe. Everything to the right of the line feed / carriage return
disappears.
An example:
"BOCA RESTAURANT GROUP LLCDBA BOCA3200 MADISON RDCINCINNATI, OH45209"

Becomes:
BOCA RESTAURANT GROUP LLC

In the original Crystal Reports 7 report, before being export by Crystal,
the entry was:
"BOCA RESTAURANT GROUP LLC
DBA BOCA
3200 MADISON RD
CINCINNATI, OH 45209"

I hope someone can figure out this "feature". I have another few thousand
records in another report that need to be parsed, cleaned, and printed.

No response from OP as yet.


Gord

On Wed, 14 Feb 2007 08:01:30 -0800, "RagDyeR" wrote:

Hey Gord,

Please let us know if that invisible character is *not* 0010.