Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent Excel to paste data in separate cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent Excel to paste data in separate cells
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 DataText to Columns. Sometimes it will split to columns because the last time you used that function you had it set for delimited byspace. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent Excel to paste data in separate cells
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. wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to prevent Cut&Paste to change style and data validation on protected sheet | Excel Discussion (Misc queries) | |||
How to prevent Cut&Paste to change style and data validation on protected sheet | Excel Discussion (Misc queries) | |||
Prevent changing size when copy&paste into another Excel Worksheet | Excel Worksheet Functions | |||
How to paste data into multiple cells in Excel | New Users to Excel | |||
Select an array of cells and paste to separate worksheet - 2nd post - 1st didn't work!! | Excel Programming |