Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

Excel 2007 - pasting into cell changes format

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
Morya1
Asked:
Morya1
  • 9
  • 4
  • 2
1 Solution
 
TommySzalapskiCommented:
Right click, hit 'paste special' and select 'values'
0
 
TommySzalapskiCommented:
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
 
Morya1Author Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MickyJWCommented:
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
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
Morya1Author Commented:
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
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
Morya1Author Commented:
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
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
MickyJWCommented:
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
 
Morya1Author Commented:
Looks like the best solution to me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now