Solved

Excel 2007 - pasting into cell changes format

Posted on 2010-11-24
15
424 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
  • 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 500 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now