Merging Two Cells and Keeping the Values

[Guest]

I have two one cell that I am using Cell A. I am typing giftcard numbers in
the cells as 6003860012345678. The giftcard = 16 digits. I need it to
default and place 3 zero's at the beginning of each of the giftcard numbers
like "0006003860012345678". The giftcard should equal 19 digits with the 34
zeros out in front. I tried merging them. Is there a way to do this?

Sincerely,

Raymond

 

[Guest]

Have you tried using a custom format on the cell with nineteen zeros?

 

[Guest]

Would I just go to Format Cells, Number Tab, Custom, and type 19 zeros in
there?

 

[Peo Sjoblom]

Since Excel only has 15 digits of precision that won't work, only way is to
format as text or precede entries with an apostrophe '
one can then concatenate them

="000"&A1

where A1 holds the text formatted gift card number

 

[Guest]

Yes, but hilight the column or cells that you want formatted this way.

 

[Guest]

Awesome that works! Now what if there are already giftcard numbers in there
and I am not typing it each time? Is there a way to do this. I tried to
highlight the whole column and choose that format but it did not work. It
allowed me to type new values in these cells which were then like
0006003860012345678. Thank you seriously for your help until now. How crazy
it would have been that simple. Now I have all these spreadsheets with the
numbers that I may need to type all over again.

 

[Peo Sjoblom]

It is not that simple, check and you'll see that your number has changed, it
is not
6003860012345678

Excel has only 15 digits precision and will truncate numeric entries, you
need to format as text

 

[Guest]

You've lost me! It appears to work on my screen -- are you referring to the
actual data -- not just what is appearing on the screen?

 

[Guest]

It works now. You are awesome!!! The both of you

 

[Guest]

Ok -- I follow you now -- sorry! I didn't notice it was dropping the last
number.

 

[Peo Sjoblom]

type 1234567891234567 in an empty cell, then look at the value in the
formula toolbar and you will notice that the last (16th) digit has been
replaced with a zero. Go to help and look up specifications, look at the
calculation specifications and notice 15 digits of precision, that is why
one need to use text formats for all numbers with more than 15 digits