Articles

microsoft excel copy and paste problems

May 29, 2010 at 11:07:27
Specs: Windows XP

Hi,

I was very happy copying and pasting my bank statements onto a 2003 Excel spreadsheet - so I can keep good records for business. I used to highlight a section of my bank statement, say 6 rows x 5 columns, copy it, then paste into 2003 Excel - no problem. The data would appear in the right cells - 6 rows x 5 columns.

Now I’ve started to use 2007 Excel. When I try to paste the same data into a 2007 spreadsheet it populates just the first column. I get all my data in column A. I've tried Paste Special - no joy. I don't know what to do or if anyone else has this problem? It's making my work really hard and very time consuming.

Any help would be a life saver!

Thanks a lot

Paul


See More: microsoft excel copy and paste problems

Report •


#1
May 29, 2010 at 12:04:11

Under the DATA tab, try Text To Columns.

Highlight the data
Select DATA
Select Text To Columns

Read the directions on the wizard that appears.

MIKE

http://www.skeptic.com/


Report •

#2
May 29, 2010 at 15:00:52

Hi Mike

Thanks a lot for the advise. I tried your suggestion but with no joy. It seems that whatever parameters i set it doesn't recreate the data as i want it. It's a real pain as it automatically did it for me in 2003 Excel.

Thanks again for your advise - much appreciated... if you've any more ideas i'd be more than grateful Mike!

Cheers
Paul


Report •

#3
May 29, 2010 at 15:18:34

How is your data being input, what does it look like?
An example might help.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 1, 2010 at 12:59:10

Hi Mike

I've just tried to paste a part of my statement into this text box and strangely it acts the same way as Excel - it puts all the info into one long column. I'll paste if for you now just so you can see how it arranges it:

13 Apr

DR

PRE-NOTIFIED

50.00


13 Apr

VIS

THE ROYAL OAK

28.95


1129.81

D

15 Apr

ATM

CASH LLOYTSB APR15

20.00


1149.81

D

16 Apr

ATM

CASH CO-OP APR16

30.00


16 Apr

VIS

ASDA PETROL

48.82


1228.63

D

19 Apr

VIS

LODGE TYRE CO LTD

24.99


19 Apr

VIS

MORRISON PETROL

6.16


1259.78

D


Strange isn't it- and not in the least bit helpful for me! It's proving to be a real pain. As ever, and ideas would be great!

Cheers
Paul


Report •

#5
June 1, 2010 at 13:31:39

Definitely a problem.
The only thing that comes to mind is the Clipboard your using.
There is a Windows Clip board and an Office Clip board.
Try using the Office Clip board to copy the data.
Select data and to copy press [Ctrl][C][C], that’s double C
I’m not sure if will make any difference, but it’s worth a try.

The only other alternative is probably going to be some type of VBA solution.

MIKE

http://www.skeptic.com/


Report •

#6
June 1, 2010 at 13:36:43

You could also try pasting into Word and see what type or characters are following each section.
If they are carriage returns or tabs or something else.

MIKE

http://www.skeptic.com/


Report •

#7
June 1, 2010 at 13:59:44

Try copying everything in a section except for the last character.

Select a section and then carefully back your mouse up until the last character is no longer selected.


Report •

#8
June 2, 2010 at 04:33:01

Hi,

Have you looked to see if your bank's website offers a 'Download transactions' function.

If it does then you should be able to get data in a more spreadsheet friendly format.

Interestingly I tried a copy and paste directly from my Banking web-page and it copied OK into multiple cells in both Excel 2003 and 2007. Have you tried an alternate browser (e.g. Opera) - I doubt it would make a difference - but perhaps worth a try.

Finally you say in response #2 that Mike's suggestion did not work. I am quite surprised that it did not work. The Text to columns function should be able to split text in one cell (on each of several rows) into multiple columns.

Did you use the 'Delimited' option?
After selecting Delimited and 'Next' check space or tab and see what happens. Vertical lines appear where the data will be split.

Regards


Report •

#9
June 2, 2010 at 07:56:40

If your data is configured as in Response #4, then Text to Columns is not what you need, Transpose is.

But that is not going to help much if you have a large number of transactions to work with as each transaction will need to be "transposed" individually.

MIKE

http://www.skeptic.com/


Report •

#10
June 2, 2010 at 08:03:07

re: as each transaction will need to be "transposed" individually...

... or "transposed" via VBA which could be done as long as we can key off of something that "delimits" the beginnning and end of each transaction.


Report •

#11
June 3, 2010 at 01:54:23

Hi Guys

I tried the Copy using [Ctrl][C][C] but with no joy. All it seemed to do was eliminate the spaces between the data in the column i.e. they appeared bunched up in the column rather that spaced out as shown in my example above.

I also re-tried to fix it using the DATA tab. Still wouldn't put things in the right order.

I've also looked to see if HSBC offers a 'Download Transactions' facility, but they don't. It's so frustrating to have been able to do this for so long... and then get a problem with Excel 2007! I know it'd not my statement that's changed as i tried it on my father-in-laws PC with Excel 2003 and it's fine. I know i could copy and paste it onto his Excel 2003 then email it to myself, but this work around isn't practical.

As for the Transposed solution... i'm not that clued up on what that actually means!

Thanks for all the advise guys - maybe something will come up!

Cheers
Paul


Report •

#12
June 3, 2010 at 04:20:31

re: As for the Transposed solution...

Excel has a PasteSpecial feature that transposes Rows to Columns and Columns to Rows.

e.g. Select A1:A5 and Copy the range.

Select B1 and use Edit...PasteSpecial, click the Transpose option at the bottom of the dialog box.

The data will be pasted into B1:F1, instead of B1:B5.

As I said in Response # 10, a macro could be written to transpose each transaction from a column into a row as long as we can key off of something that "delimits" the beginning and end of each transaction.


Report •

#13
June 3, 2010 at 07:10:41

they appeared bunched up in the column rather that spaced out as shown in my example above.

Could you be a bit more specific.
Was the data contained in only one cell? Two Cells? "Bunched up" is a bit nebulous........

Did you try copying to Word to see what control type characters were present? IE Carriage Return as opposed to Line Feed, or Tabs as opposed to spaces.

MIKE

http://www.skeptic.com/


Report •

#14
November 15, 2010 at 10:32:26

I had this problem, too. It turns out that Excel 2007-2010 'remembers' the delimiter you use for text imports or text-to-columns conversions. So if you copy/paste tab-delimited data, but your last text conversion was space-delimited, Excel will attempt to paste your data as space-delimited even if it has tabs.

A possible solution (assuming your data is tab-delimited): copy some of your banking data into a worksheet and use text-to-columns to convert it using a tab delimiter. Then delete what you've just done and re-paste your data. It should paste tab-delimited now.


Report •


Ask Question