Text to columns

  • Thread starter Thread starter davehall
  • Start date Start date
D

davehall

Hi, Daily we open a .lst file, which is a text file, which used to put each
line of data in to the first cell in a row. We then used the Text to Columns
feature to split in to individual cells. Within the last week, when we open
the file it splits in to individual cells automatically but doesn't split as
requried. I can only assume that a recent Excel update has introduced this
and wonder if anybody knows how to disable this "feature".

Dave - 17/3/2008
 
It depends on the data if it is put in one or multiple cells. Check some of
your older files to see if the same thing happens. It is possible the source
of the lst file has changed and not excel.

You can use the menu option Data - Import External Data - Import Data which
will do the same thing as the Text to columns.
 
The format of the data hasn't changed at all. We have checked some older
files that were fine at the time but aren't now, hence the assumption that
something has been changed within Excel. We've also tried this on a number of
PC's all with the same results.
 
How do you open this file?

Are you opening it in NotePad (or another text editor), then copy|pasting into
excel?

Then using Data|Text To Columns

If that's what you're doing, then you're seeing a helpful (sometimes!) feature
of excel.

It remembers how you parsed the data the last time you used data|text to
columns.

You can force excel to forget this memory by closing excel and reopening it
or
you could do a "dummy" data|text to columns.
Put a string (asdf) in an empty cell.
data|text to columns|delimited|uncheck all the delimiters
and then clean up that cell.

=========
I've never seen excel not prompt the user with the text to columns wizard when
they open a text file. (This doesn't apply to .csv files.)

If I guessed wrong (and just haven't seen your situation), then there's another
option.

Open Excel
File|Open
Select your .lst file
But hold the shift key when you click the Open button.

You won't see the text to columns wizard and everything should go into column A.
 
Hi Dave,

I have run into this sort of behaviour with Text to Columns before,
and I am sure it is part of the Excel being helpful syndrome.

I don't actually understand the why's and how's of how it works
but the cause is most likely someone saving this behaviour to
your default template, or something along similar lines.

Try opening a virgin file and see what happens there.

HTH
Martin
 
Hi Martin

I'm convinced that this is a Microsoft Office update "feature" as I've tried
opening many different files on many different PC's and have seen the same
behaviour each time!

We can work around the problem but it is a bit infuriating that something
has apparently changed because MS thought it a good idea!

Thanks


Dave
 
Hi Dave

We open by right-clicking on the file and selecting "Open with..".

I can open in Notepad and cut and paste in to Excel and it's fine but it
makes it a bit long-winded and is a step back from what we could do before.

Tried your suggestion to make Excel "forget", but it makes no difference.

Regards

Dave
 
The import wizard starts, I choose fixed width and the column markers Excel
decided to apply match those that apply if I just open the file using
right-click etc.
 
And if you use File|Open and shift-click on the Open button?
The import wizard starts, I choose fixed width and the column markers Excel
decided to apply match those that apply if I just open the file using
right-click etc.
Click to expand...
 
Ah, that does the job. Opens without starting the wizard and puts everything
in to the first column.

Still doesn't answer why we can't do what we used to do though!
 
I don't have a guess for that. But I rarely use "Open With" to open text files
in excel.
Ah, that does the job. Opens without starting the wizard and puts everything
in to the first column.

Still doesn't answer why we can't do what we used to do though!
Click to expand...
 
Back
Top