How to prevent Excel to paste data in separate cells

[azu_daioh]

I dont know why this is happening sometimes. We have a database
(created from COBOL?) where I usually copy a payment history screen
onto Excel for when I need to create a report.

Sometimes when I capture the data such as this from the Cobol DB:

P 120107 010708 038 07164728 1796.86 01 10 08 02
T 112407 113007 007 06558723 331.00 12 04 07 02


It gets copied onto Excel in separate columns (-- is where the colum
breaks)
P -- 121007 -- 010708 -- 038 -- 07164728 -- 1796.86 -- 01 -- 10 -- 08
-- 02

and sometimes it gets copied into one column and I could use the text
to column to sparate them.

I prefer it when it gets copied into one column because I could easily
change the dates into date formats. The problem I'm encountering when
Excel copies the last 3 sets of digits into 3 columns instead of one.
10 -- 08 -- 02 is a date field and it involves a lot of reformatting
(Concatenate, then text-to column, etc) before I could format it to
date.

Does anyone know if there is a setting somewhere in Excel where I
could paste each line of data onto one cell? I dont understand how
Excel sometimes pasted the data into multiple columns and sometimes
into 1 column.

When Excel automatically splits them into multiple cells, I have to
paste the data first into word and then copy from Word and paste into
Excel. Too many steps. I'm trying to write an instructions for my
colleagues whom are not very computer literate so I want to make it as
simple as possible without them typing the data one at a time

Thank you,

Sharon

 

[Gord Dibben]

Sharon

Does anyone know if there is a setting somewhere in Excel where I
could paste each line of data onto one cell? I dont understand how
Excel sometimes pasted the data into multiple columns and sometimes
into 1 column.

Excel tries to help you by remembering the last set in Data>Text to Columns.

Sometimes it will split to columns because the last time you used that function
you had it set for delimited by>space.

Next time it will leave it one column because delimited by space was not left
over.

It is a crap-shoot unless you make sure beforehand that the settings are set to
"Fixed Width"

You can ensure that by first running something through using that setting.

Or do it in code when you copy from the DB.


Gord Dibben MS Excel MVP

 

[Damien McBain]

If you use "paste special" / "text" it should always place each row in 1
cell. Then you can use text to columns to split it.