Auto Text to Columns

[mr.c]

Sometimes (I can't figure out what triggers it) Excel 97 remembers the parameters that I used to parse some text into columns, space delimited, for example. Then the next time that I paste some text in, it automatically parses the data with those same parameters immediately when I hit Ctrl-V. I'm pretty sure that the last time this happened, closing and restarting Excel wasn't enough to "clear the memory." I had to restart the whole computer. I can't find anything in the menu options or preferences about this, either. Since I pull data with SQL/SAS and paste it into Excel all of the time, this is a bit of a pain. Does anyone have some insight?

[NoName]

There's probably a better way, and I'm not in the office now so I can't test it out, but I think if you do a text-to-columns on a couple of dummy cells with the parameters you would want to use that may help. This is an annoying "feature" of Excel, I agree with you.

[Dr T Non-Fan]

It helps when you're importing many data with the same delimiters. It means not having to bother with setting the same delimiter.

It's a bit unnerving, though when afterward you want to copy a sentence into a cell, only to remember that your delimier was a space.

Otherwise, you'll have to close Excel, then reopen. It starts fresh then.

[mr.c]

That's true, DTNF, but then why doesn't it do it with fixed width parsing, as well. I haven't learned my SAS macros yet, so it would have been nice to not have had to choose the same five column dividers every time.

[Dr T Non-Fan]

You can add a delimiter in your SAS data extracts. Choose something and stick with it, and make sure that no other field will use it in their values. The comma (,) is risky, as your number fields might come with thousands/millions dividers in the values.

[mr.c]

So you always create text outputs (with put statements?) instead of using proc print or means?

[Dr T Non-Fan]

"Tell him, Raymond."
"Proc Print sucks."

It was a while ago (eight years?), but I recall some kind of "output=(filename)" statement. You can change the format of the fields and all that.

[Gordo]

In access you can use "import specifications". It's exactly like the "text to column" function in excel but can be saved and reused. It you're parsing the same data formats it might be the way to go. You can even create a macro that spits out an excel file.