Text to columns

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

On Office 2003/Excel when i use the Text to Columns
feature to separate text from one column into others, the
text right to the separator character dissapears. How can
I avoid this? In other versions I have never encounter
this problem.
 
Hi Rick
never saw this before (using Excel 2003). Can you post an example of
the data you have used (plain text - no attachment please)
 
Hi Rick
In step 3, does the column heading in the data preview say "skip column"? If so, select that column and click one of the other options under "column data format

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Rick wrote: ----

On Office 2003/Excel when i use the Text to Columns
feature to separate text from one column into others, the
text right to the separator character dissapears. How can
I avoid this? In other versions I have never encounter
this problem
 
Frank,
Here's an example: This text: "80 City Square
Boston, MA 02129" having chose on Step 1,Delimited as
Data Type, becomes this: "80 City Square" checking any
box except Space on Step 2. By Step 3, the result is
already cut off.
And to Mark, because my line above it wont work.
 
Mark,
That's a good tip! Except I work on a laptop that has no
numeric pad and it does not work. Do you know a way
around it?
Thanks
Rick
-----Original Message-----
Hi Rick,
The problem is probably caused by carriage returns.
Click to expand...
When you start the text to columns you will see a thick
line character before "Boston", or it will be evident
because the cell wraps after the street address.
You need to remove the carriage returns in order to do the text to columns.

1) Select the address cells and Edit>Replace
2) With the cursor in the 'find what' field, hold the
Click to expand...
alt key and type 010 on the number pad (not the top of
the keyboard) You won't see anything in the field but
the carriage return is there.
3) Leave the 'replace with' field blank or enter
Click to expand...
whatever delimiter you want to use.
 
Hi Rick
You can use a helper column and the following formula

=SUBSTITUTE(A1,CHAR(10)," "

Change the " " to whatever delimiter you want to use. Then Copy and Paste_Special>Values. This will remove all of the carriage returns

Another option would be to split up the addresses with LEFT and RIGHT functions, instead of text-to-columns

=LEFT(A1,FIND(CHAR(10),A1)-1
=RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1)

Hope one of these helps

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- (e-mail address removed) wrote: ----

Mark
That's a good tip! Except I work on a laptop that has no
numeric pad and it does not work. Do you know a way
around it
Thank
Ric
-----Original Message----
Hi Rick
The problem is probably caused by carriage returns.
Click to expand...
When you start the text to columns you will see a thick
line character before "Boston", or it will be evident
because the cell wraps after the street address
2) With the cursor in the 'find what' field, hold the
Click to expand...
alt key and type 010 on the number pad (not the top of
the keyboard) You won't see anything in the field but
the carriage return is there
3) Leave the 'replace with' field blank or enter
Click to expand...
whatever delimiter you want to use
 
Mark,
Thank u! The last tip, works better and it's worth the
little extra work.
Substitute works but the result doesn't, with Text to
Columns. Getting rid of those pesty Returns is not worth
the effort when there's other way.
Thanks again.
Rick
(e-mail address removed)
Edison , NJ
-----Original Message-----
Hi Rick,
You can use a helper column and the following formula:

=SUBSTITUTE(A1,CHAR(10)," ")

Change the " " to whatever delimiter you want to use.
Click to expand...
Then Copy and Paste_Special>Values. This will remove all
of the carriage returns.
Another option would be to split up the addresses with
Click to expand...
LEFT and RIGHT functions, instead of text-to-columns:
 
Back
Top