In Excel, can anyone tell me why this happens... 2

[hneal98]

In Excel, I have a formula that gives the results of currency. I copy the results and paste them in other cells that are formated to currency, however, after pasting the data, the cells do not reflect the format. I have to press F2 and then press ENTER before it shows the format.

I have looked all over trying to figure this out, so if anyone knows why it does this and how to fix it whithout having to go to each cell to press F2 and ENTER, I would greatly appreciate it.

Thanks in advance...

 

[xlbo]

what is the original cell formatted as ??

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[DPlank]

Sounds like the calculation is set to manual to me.

Check Tools -> Options -> Calculation tab and make sure the Automatic calculation radio button is selected.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[hneal98]

Probably text, but that should not matter, should it?

As for the auto calc, that is already set. I thought of that as well, but it was set.

 

[hneal98]

I just looked at the formatting and it was not set to any specific format, so I can assume it is General.

 

[gizmo1973]

IN that case when you paste you are pasting the format as well as the data, you will have to contine to re-format as currency or paste values only

Regards, Phil.

We're Coming To Get You!!!!

"Where there's blame there's DPlank"

 

[eyec]

when you past it use the Paste Special - Paste Formula

 

[hneal98]

I do paste value, it seems that it should not matter however, becuase once I paste it and then try to change the format of those cells, shouldn't it change the the format of that I set them to?

As for eyec, why would I paste formula when I want just the value?


Thanks for all your responses.

 

[misscrf]

You need to choose paste special - values and number format.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[hneal98]

I did that too, but no go. the formatting is there, but I have to hit F2 and ENTER before it shows on the screen.

 

[xlbo]

really does sound like either your copy cell or your paste cell is formatted as text

The reason I say this is that any cell formatted as text will not accept a formula entry - it will just show the text of the formula rather than the result. The standard workaround is to make excel re-evaluate the cell - you do this by pressing F2 and enter (which is what you have been doing).

Please check again the source and paste cell formats

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[hneal98]

I understand what you are saying, but I not sure you understand what I am saying.

The formula works fine, it shows the data I need. When I copy the data, I use PASTE SPECIAL and then paste it as a value, however, that value is currency. When I past the data (regardless of what format the cells are in,) it is treated like text unless I hit F2 for that cell. If I have 10,000 records, I don't want to have to hit F2 for every record. That is what I am trying to avoid. I need to find a way to make Excel use the format that the cells are set to.

I kind of feel like I just rambled on. Hopefully someone can understand what I just said.

 

[xlbo]

In which case it STILL sounds like your DESTINATION cells are formatted as text

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[EndoMan]

Not sure why you are having the formatting problem, but you may want to try the following to quickly get the pasted data reformatted (as opposed to hitting F2 and ENTER for each cell):

1) Enter the number "1" in a cell (make sure it is formatted as a number)
2) Copy the cell
3) Select the entire range of Pasted data
4) Choose Edit/Paste Special.../Multiply, then OK
5) Reformat the cells as currency

The Paste Special.../Multiply forces the cell to think it is a number.

 

[hneal98]

Sorry, it did not work for either suggestion. I guess it is just a bug with Excel. I tried formating the field that the formula was in and the field I was going to copy the value from the formula to and no dice. Here is an example of the data in one field and the formula to strip out the $ amount...

Here is an example of the data:
RMR*AZ*1111111111111111**57.21~

Here is the formula:
=MID(A8,FIND("**",A8,1)+2,LEN(A8)-(FIND("**",A8,1)+2))

Thanks again...

 

[xlbo]

Your problem is that you formula returns a string - ie text

amend to this and you should see a resolution:

=VALUE(MID(A8,FIND("**",A8,1)+2,LEN(A8)-(FIND("**",A8,1)+2)))

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[hneal98]

My friend xlbo, I shall never doubt you again.

It worked perfectly.

Thank you very much.

 

[hneal98]

Value function converts a text value that represents a number to a number.

http://www.techonthenet.com/excel/formulas/value.php

 

[xlbo]

no probs - glad we found a resolution

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question