Plagued by recurring parses...

are you doing this using vb code? if so, post up your code so we can take a look....

This has been bugging me for a while, specially when pasting sample data from the Board.

I opened NotePad, typed in 123 456 789, copied it to the Clipboard, right clicked cell A1 in a blank worksheet and chose Paste Special, Text. It all appeared in one cell so I chose Data, Text to Columns, specified Space as the delimiter and clicked finish. Now it was in 3 columns as I wanted.

I right clicked cell A2 and chose Paste Special, Text. The data appeared in 3 columns - Excel-parse-dejavu as you say.

So I selected a single cell with some data, chose Data, Text to Columns, cleared the Space check box and clicked Finish. I right clicked cell A3 and chose Paste Special, Text. Bingo - it all appeared in one cell!

Thanks for prompting me to find a solution.

I'm not sure I understand. Is your problem that:

1] you do a text-to-columns paste (lets say, 3 text values in A/B/C), then
2] on the same sheet, same area, do another, over-writting, text-to-columns, where
3] the new data is *supposed* to be text number text, but it still
4] shows up as text text text

Is this the problem you are describing? Or is Andrew's problem what you are experiencing? Sorry for being dumb... still on 1st cuppa java.

just_jon said:

I'm not sure I understand. Is your problem that:

1] you do a text-to-columns paste (lets say, 3 text values in A/B/C), then
2] on the same sheet, same area, do another, over-writting, text-to-columns, where
3] the new data is *supposed* to be text number text, but it still
4] shows up as text text text

Is this the problem you are describing? Or is Andrew's problem what you are experiencing? Sorry for being dumb... still on 1st cuppa java.

Click to expand...

Jon,

The OP said "I find that when parse data (text to cols), whenever I copy new data, Excel automatically parses to the previous parse format."

I take that to mean the scenario I described.

Thanks, Andrew! I may need to get that 2nd cup in me sooner than later!

Parse dejavu solved..

Mr. Andrew Poulsom was kind enough to solve my problem. See his post -- it also includes a rather elegant demonstration of the exact problem I've been having!

After looking at the mechanics of the situation, I relized that Excel - in it's fuzzy logic world - ASSUMES that you'll want to use the same parsing criterion on subsequent parse operations. In my case this is certainly not true!

To "reset" the parse specifications, one would have to access the dialogue box and "Uncheck" all specifications. This is what Mr. Poulsom does in his example.

Taking it one step further, a call to the code I've included below (sub ParseSnip() ) will do the same. Using it BEFORE a Text to Col operation will prevent parse-dejavu!

Thanks, again, Andrew
====================================

Sub ParseSnip()
' Resets Parse Specification after text to columns
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
End Sub

How about the oposite of this??? If I keep the Excel spreadsheet open the "Text to Columns" settings are mainted for me as I paste into and "save as" the workbook, but when I close and reopen the sheet (the next day), it reverts back to the default settings. Is there a way I can make the selections I choose (space delimited) save as the default for this workbook, so that who ever opens it gets these parsing options?