formatting cells

[philibe]

Hi all.

I'm currently trying to find a way of copying exact contents of cells after formatting them.

eg.

A cell contains "1890"
I then right click and select 'format cell'
Under 'Number' i go to custom and write the format ..: "00####"
This makes the cell appear as "..: 001890"

Now, is there anyway of copying the cell so that when i paste it, it appears as "..: 001890" in the bar above the worksheet where you can input the cell contents. Rather than appearing as "..: 001890" in the cell, but 1890 in that bar which is what happens when i copy and paste as normal. I have tried copy -> paste special -> values but it doesn't work.

Any help would be much appreciated.

Thanks.

[gurusanthanam]

You can do it...in an indirect way...

if the Cell "A1" contains that '1890' formatted with "00####", then if you want in "B1" the value...'001890'...here is the way....

type the following formulae in B1...

=TEXT(A1,"00####")

then copy the cell "B1" and do paste special value....it will work...

Hope this helps you....

Regards,
guru

[philibe]

Thanks

Is there anyway of doing it to include the "..:" prefix. I know it sounds really random, but it would help me loads.

So if A1 is formatted "..: "##0000
can you return a cell with the exact value?
I tried adding it on using =TEXT(A1, "..: 00####")
but it came up as #VALUE!

[gurusanthanam]

i hope you can do it with this formulae...

="..:"&TEXT(B1,"00#####")

whatever u want to prefix, just add that within quotes and place it with an operand (&) in front of the formulae.....

if you do paste special value...it will work now...

Regards,
guru

[philibe]

Thanks so much!!