Gap or something I cannot understand

[MFS]

Hi,

May be this is a gap or it is something I cannot understand.

Every day I copy some data from a system to Excel (copy/paste method).
Normally every line in the system will be completely pasted in the first
column’s cell (A).

After that I use Text to Columns and everything is fine, the problem starts
after using Text to Columns. When I copy new data, it does not be pasted in
the first column’s cell (A) “as usualâ€, but the data jumps from the first
column to the others.

The only way I know to resolve this problem, is by closing all Excel
applications and reopen a new one.

Please help me on this.

 

[Dave Peterson]

Excel likes to help.

And sometimes (when the new data to be parsed is laid out just like the text to
columns you just used), it's very helpful.

Other times, it's just annoying.

You can close excel (yechh!)

Or you can do a dummy text to columns.

Find an empty cell
Put some junk in it (like: asdf)
Then do data|text to columns
Chose delimited by
But uncheck all the options
finish up the wizard
Clear that cell.

And then do your copy|paste

ps.

If your data is all the same, it may be helpful.

Or just do all the copy|pastes, then do a single text to columns????

 

[Gord Dibben]

Excel remembers the last setting you used in Text to Columns.

Without closing Excel, open Text Wizard and clear the delimiters previously
used.

If you're doing this a lot maybe this John Walkenbach macro will help you.

Sub ClearTextToColumns()
On Error Resume Next
If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"
Range("A1").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:=""
If Range("A1") = "XYZZY" Then Range("A1") = ""
If Err.Number <> 0 Then MsgBox Err.Description
End Sub

Place it in your Personal.xls file.


Gord Dibben MS Excel MVP