Text to Column effecting automatically

[Hari]

Hi,

Let's say I copy following text from some ASCII file and paste in to
excel.

Wer-67843-PLOU
Qty-34r5-ASDE

Then I use Excel's text to column feature on above and parse it with
Hyphen as delimter.

Wer 67843 PLOU
Qty 34r5 ASDE

Well and Good.

Next time I copy text with similar structure from ASCII file and paste
in to the ABOVE excel SESSION the parsing happens automatically.

Now, sometimes I would like this to happen automatically while
sometimes not (In the second case I have to go to the trouble of
starting new excel sessions).

Can this be feature be toggled on or off at user's whims?

Did MS DESIGN this auto parsing feature with a definite purpose?

Regards,
Hari
India

 

[CLR]

It doesn't work that way for me in Excel 2000. The TextToColumns feature
only affects the values I apply it to at the time, no residual action
here.........which version of Excel are you using?

Vaya con Dios,
Chuck, CABGx3

 

[Hari]

Hi Vaya,

I have tried it in 2002 and 2003. It works the way I said it. (Win XP
as OS).

Regards
Hari
India

 

[CLR]

Hi Hari...........

Hmmmm..........well, I don't use either XL02, XL03 or WinXP, so I can't
check it out there, but perhaps someone else can jump in here and try it on
their system..........sorry, and Good Luck finding the problem. I'll be
watching this thread for the solution because my company may upgrade
someday, and if this is a built in bug, I would like to know about it.

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

I think that this was designed as a feature (my guess only).

If you do this a lot in the same session and always use the same parsing rules,
then it makes life a bit easier.

To "clear" excel's memory, you can close excel and reopen it.

Or you could do a dummy data|text to columns.
choose delimited and uncheck all the delimiters.
and finish up.

 

[Paul D. Simon]

Try this macro (posted by another user some time ago). I've got mine
assigned to a custom button on my toolbar.

Sub TextToColumnsReset()
Dim DeleteCell As Boolean
If ActiveCell = "" Then
ActiveCell = 1
DeleteCell = True
End If
ActiveCell.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False
If DeleteCell Then ActiveCell.ClearContents
End Sub

 

[Hari]

Hi Dave and Paul,

Thnx for your comments and solution.

Regards,
Hari
India