text-to-column problem

C

CraigR53

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?
 
M

Miguel Zapico

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.
 
P

Paul Dusterhoft

In the "delimited" section, "check" other and enter "0010", using the
number pad while holding down the Alt key
 
C

CraigR53

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?
 
M

Miguel Zapico

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.
 
G

Gord Dibben

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

Delimited by>Other ALT + 0010 Or CTRL + j


Gord Dibben MS Excel MVP
 
R

RealGomer

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

Gord Dibben

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


Gord Dibben MS Excel MVP
 
R

RealGomer

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

RealGomer

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)
 
R

Ragdyer

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.
 
R

RealGomer

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.
 
G

Gord Dibben

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
 
R

RagDyeR

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
 
Top