Formatting Cells

[Katy]

I want to format cells to automatically enter a hyphen
between every four numbers: i.e., xxxx-xxxx-xxxx-xxxx,
like a credit card account number. When I do this, the
last number is automatically rounded up or down. How can
I format the cell so that Excel sees the numbers as text
and automatically inserts the hyphens between the numbers?

Any suggestions?

Thank you.

Katy

 

[Peo Sjoblom]

No such format I'm afraid, you have to use text format and type in
everything
(dashes included) since excel can only show 15 digits or you have to use
an event macro that will do it for you

 

[RagDyer]

Hey Kathy,

If you don't mind using a "helper" column with a text formula, you can enter
your characters, and three dashes will be inserted automatically.
Actually, that's all it really saves, entering the dashes !

With your data in column B, enter this formula in column A, and copy down as
needed:

=IF(B1<>"",LEFT(B1,4)&"-"&MID(B1,5,4)&"-"&MID(B1,9,4)&"-"&MID(B1,13,4),"")

Column B would have to be pre-formatted as text so that it could accept 16
numerical characters, without changing the last to 0's or, you would have to
enter an apostrophe first, before the first number, which would be invisible
in the cell.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Dave Peterson]

Didn't that last digit (in the one's position) always change to 0 when you had
it General or that format?