?
Solved

Excel 2007 - pasting into cell changes format

Posted on 2010-11-24
15
Medium Priority
?
439 Views
Last Modified: 2012-05-10
I can't stand how when I copy and paste long numbers into excel the format of the cell changes so that it either changes the last digit to 0 or puts it into scientific notation. So then what I have to do is go to the column, select it and change the format to text and then go through each and every cell to correct the value of the cell to the correct number.

I'm using credit card numbers so this happens ALL THE TIME!!!

It is equally as frustrating when excel rounds off the dollar amounts (or euros) so that when I paste in 2000.00 it rounds it to 2000 or when I paste in 444.40 and it rounds it to 444.4. (I need two decimal places showing when I click in the cell)

Is there a way to make permanent the formatting of a column, or a page,, so that when values are pasted into it by any means whether that be by copy and paste or by macro or function the format of the pasted-into-spreadsheet does not change?

Drives me nuts!
0
Comment
Question by:Morya1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 2
15 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34209689
Right click, hit 'paste special' and select 'values'
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34209695
I do it all from the keyboard.
Right click button (usually in between the right ctrl and alt keys)
S
V
[Enter]

Or you could make a macro that does
Selection.PasteSpecial xlPasteValues
And attach a shortcut key to that.
0
 

Author Comment

by:Morya1
ID: 34209716
Thanks, Tommy, but do I have to do that for every value I copy and paste? And that won't work for values that are imported by a macro. And sometimes I drag a value into a cell so that won't quite do it.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:MickyJW
ID: 34209751
The code syntax for changing a cells formatting value to text is Selection.NumberFormat = "@"
To change it to a currency format it is Selection.NumberFormat = "$#,##0.00"
You can easily find these out or make macros with doing them by recording a new macro and making the change once.
I would then advise mapping each macro to a hotkey.
The code for these macros work off your current selection, so all you do once you have it setup is select what you want converted, then hit the hotkey...and your done!
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34209762
If you drag a value, then you drag the formats with it. That's all there is to it.
For "values that are imported by a macro" the macro can use PasteSpecial as well.
There is no good easy way to make Excel default to using paste special.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34209772
Now, you could use the Worksheet_Change macro to reset the formatting of a column every time a cell in that column is changed. This would effectively "make permanent the formatting of a column" as you requested. But you'd have to set that up for every sheet.
0
 

Author Comment

by:Morya1
ID: 34209794
I like the macro idea but I really need it coded for me so I can just plop it in and know where to plop it.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34209811
Put this in your XLSTART folder
C:\Program Files\Microsoft Office\OFFICE11\XLSTART

Of course, if you have 2007 use the Office12 folder etc.

I mapped it to Ctrl+Shift+V
PERSONAL.XLS
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34209819
If you already have a personal.xls folder there, open it and add this to one of the modules, then assign the shortcut key you want
Public Sub PasteValues()
  Selection.PasteSpecial xlPasteValues
End Sub

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34209828
You may have to hide it the first time. If PERSONAL.XLS opens when you open Excel, click on it then click Window->Hide
0
 

Author Comment

by:Morya1
ID: 34209886
trying to save personal.xls to the xlstart folder but it won't let me save there and I am logged in as the admin. Also, how do you assign a short cut key to it? Raising points to 500
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34209899
Click 'macros' click the name of your macro and hit 'Options' then type what you want in the little box (I use Crtl+Shift+V, it does the Crtl for you so you just hit Shift+V).

Save the file elsewhere and copy it to your xlstart folder from Windows Explorer.

If you get stuck tell me your Excel and Windows versions.

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34209906
Nevermind. Excel 2007. I can read!

Just for info's sake. Each user also has an xlstart folder in Application Data (or AppData) but putting it in the Office folder makes it go for every user. (It will actually copy the PERSONAL.XLS file into your user folder).
0
 
LVL 2

Accepted Solution

by:
MickyJW earned 2000 total points
ID: 34209922
No real need to code, you can record the macro without coding.
Heres how to do it:
Record the text conversion macro:
1. Select an example field you want to transform into text
2. Goto the View Tab then click on the Macros arrow (found to the right)
3. Click Record Macro
4. Name the macro to something memorable (eg. Num2Text)
5. Define a hotkey for the macro (keep in mind not to overwrite other useful hotkeys you use)
6. Click OK
7. Right Click on the selected field, go into format cells
8. Select the text formatting category, then click OK.
9. Click back on the macros arrow on the top menu (under view), and click Stop Recording
--This has now recorded your Num2Text Macro, use the hotkey to use the macro anytime---

To make the macro to convert to currency:
1. Repeat steps 1-3 in previous list
2. Name the macro to something memorable for this function (eg. Convert2Currency)
3. Repeat steps 5-7 in previous list
4. Instead of selecting the text category, select currency, making sure you have decimal places set to 2 (or whatever your preference). You can also define if the currency symbol is active
5. Repeat step 9 in the previous list
--This has now recorded your Convert2Currency Macro, use the hotkey to use the macro anytime---

In order for this to work, you must make sure you are selecting the same cell as before you start recording the macro, if you choose another cell, the code will specifically include instructions to navigate to that exact cell, which is not what you want, you just want it to work off whatever your current selection is.

Hope this helps and is a viable solution for you.
0
 

Author Closing Comment

by:Morya1
ID: 34316252
Looks like the best solution to me.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question