How to "reset" the "Text to Columns" data parsing function

[Steve_in_Montréal]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

How does one clear or reset the "Text to Columns" data parsing function?

Here's my problem:
I cut & paste some data from a PDF table into an Excel sheet.
The data fill a single column
Before I parse the data with the "Text to Columns" I perform a "find and replace" to prep by data so that it contains a common character at the places where I want it to parse.
Then, I perform the "Text to Column" functions which works exactly the way I want it to.

Here's where the problem starts:
If I paste a new set of data into the Excel sheet, Excel assumes that I want to parse it immediately. Unfortunately, I DON'T want to parse it as I paste it because I need to prep the data with "find and replace" FIRST.

In summary:
It seems that once you perform a Text to Columns data parse, Excel assumes that from that point on, you will want to perform the same Text to Column parse whenever data is pasted into the Sheet.

This problems exists for Windows and Mac versions alike.

 

[CyberTaz]

AFAIK the Text to Columns feature doesn't "assume" anything. I believe what
you're finding is that what happens when you paste depends on the structure
of what you've copied. If the copied data has an intelligible column
separator (table, tabbed, commas, etc.) Excel will recognize it & separate
the data into columns accordingly. If the data doesn't have that structure
(multiple spaces, perhaps) it gets pasted into a single column by (n) rows
based on the line break/carriage return at the end of each record. The
deceptive element is that the cause of separation in the source isn't always
readily discernable - especially in a PDF.

I don't know that you can alter how Excel responds to the pasted data. You
might try using Paste Special - maybe the options offered may provide the
solution. I haven't really had an opportunity to test. It's possible that
you may have to pass the data through a word processing environment first in
order to do your Find & Replace there.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Allenz]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

How does one clear or reset the "Text to Columns" data parsing function?

Here's my problem:
I cut & paste some data from a PDF table into an Excel sheet.
The data fill a single column
Before I parse the data with the "Text to Columns" I perform a "find and replace" to prep by data so that it contains a common character at the places where I want it to parse.
Then, I perform the "Text to Column" functions which works exactly the way I want it to.

Here's where the problem starts:
If I paste a new set of data into the Excel sheet, Excel assumes that I want to parse it immediately. Unfortunately, I DON'T want to parse it as I paste it because I need to prep the data with "find and replace" FIRST.

In summary:
It seems that once you perform a Text to Columns data parse, Excel assumes that from that point on, you will want to perform the same Text to Column parse whenever data is pasted into the Sheet.

This problems exists for Windows and Mac versions alike.

 

[Allenz]

I have had the same problem and if data you're pasting looks similar to the
date that was prevously parsed then Excel does assume that you want to parse
that data the same way.

You need to reset the separators in the Text to Columns to something that
doesn't appear in your data before pasting.