Problem with "Text to Columns" feature

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I'm having problems using the Data->Text to Columns feature. Is there a
Add-On I need that I may not have installed.

Any help will be appreciated.
 
Ed said:
I'm having problems using the Data->Text to Columns feature. Is there a
Add-On I need that I may not have installed.

Any help will be appreciated.
Click to expand...
Sorry -- I should have included that I use Excel 2003
 
What are the problems that arise?

Describe what you have and what you want and how T to C does not give you
that.

T to C does not require an Add-on.


Gord Dibben MS Excel MVP
 
For instance - I have the following in cell "A1".

"Date,Open,High,Low,Close,Volume,Adj Close
2009-01-20,849.64,849.64,804.47,805.22,6375230000,805.22
2009-01-16,844.45,858.13,830.66,850.12,6786040000,850.12
2009-01-15,841.99,851.59,817.04,843.74,7807350400,843.74
2009-01-14,867.28,867.28,836.93,842.62,5407880000,842.62
2009-01-13,869.79,877.02,862.02,871.79,5017470000,871.79
2009-01-12,890.40,890.40,864.32,870.26,4725050000,870.26
2009-01-09,909.91,911.93,888.31,890.35,4716500000,890.35
2009-01-08,905.73,910.00,896.81,909.73,4991550000,909.73
2009-01-07,927.45,927.45,902.37,906.65,4704940000,906.65
2009-01-06,931.17,943.85,927.28,934.70,5392620000,934.70
2009-01-05,929.17,936.63,919.53,927.45,5413910000,927.45
2009-01-02,902.99,934.73,899.35,931.80,4048270000,931.80
2008-12-31,890.59,910.32,889.67,903.25,4172940000,903.25
2008-12-30,870.58,891.12,870.58,890.64,3627800000,890.64
"

After I run T to C I get the heading names nicely seperated in columns but
none of the numbers come show at all.
 
Do you really have all this stuff in A1 (just a single cell) or do you have each
line in separate cells?
 
That's right - all stuffed in a single cell.

Dave Peterson said:
Do you really have all this stuff in A1 (just a single cell) or do you
have each
line in separate cells?
Click to expand...
 
Then I do the following:

Select Delimited and click Next
Select Comma and Text Qualifier (") and click Next
Select General for each column, Destination "A1" and click Finish
I get the following 7 columns only - no numbers following

Date Open High Low Close Volume Adj Close

Ed




Ed said:
That's right - all stuffed in a single cell.
Click to expand...
 
The first thing you should do is put each line into its own cell.

It looks like this could be copy|pasted from a text file (.csv???).

If that's the case, try changing the name to *.txt (if necessary), then use
file|open and parse the data that way.
 
The first thing you should do is put each line into its own cell.

It looks like this could be copy|pasted from a text file (.csv???).

If that's the case, try changing the name to *.txt (if necessary), then use
file|open and parse the data that way.
Click to expand...

A csv should have opened it properly, no? So just use file open on the csv
file and it will all be done for you. If you're copying and pasting from,
say a website, try Edit - Paste Special and some of the options you get from
that. At worst it should put each line in its own cell after which TTC will
work as you expect.
 
In my head (a scary place to be!), I was thinking that maybe the OP opened the
text file in NotePad (say) and then copy|pasted into the formula bar.

But I didn't quite make that visual clear.
 
You could try this...

Select A1 and in Text To Columns, select Delimited in Step 1, (uncheck the
Comma checkbox if it is selected) then click in the empty box next to
"Other" and press Ctrl+J and then click the Finish button. Select A1:O1
(where your data was split out to), press Ctrl+C to copy it, select A2 and
use the Transpose option from Paste Special. Delete Row 1. Select Column A
and run Text To Columns using the Comma option at Step 2 (uncheck the Other
checkbox at the same time). This should split out your data as you wanted.

--
Rick (MVP - Excel)


Ed said:
That's right - all stuffed in a single cell.
Click to expand...
 
Thanks Rick - it worked, of course. Thanks to ALL who helped.

Ed

Rick Rothstein said:
You could try this...

Select A1 and in Text To Columns, select Delimited in Step 1, (uncheck the
Comma checkbox if it is selected) then click in the empty box next to
"Other" and press Ctrl+J and then click the Finish button. Select A1:O1
(where your data was split out to), press Ctrl+C to copy it, select A2 and
use the Transpose option from Paste Special. Delete Row 1. Select Column A
and run Text To Columns using the Comma option at Step 2 (uncheck the
Other checkbox at the same time). This should split out your data as you
wanted.
Click to expand...
 
Back
Top