Or format as text, enter the string and then use a help column and something
like

=TEXT(LEFT(A1,15),"0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-")&RIGHT(A1)


and copy down



--


Regards,


Peo Sjoblom


"Ron Rosenfeld" wrote in message
...
On Tue, 16 Oct 2007 12:14:01 -0700, CNB Cheryl <CNB
wrote:

I need to have a 16 digit number with - between each number. I created a
custom format, however the 16th digit always reverts to 0 (zero). I need
this to accept all the numbers that are entered into the field. We are on
a
deadline so any help is appreciated. I have tried comma's and other
items,
but no go...

Thanks
CNBCheryl

Excel's specifications are such that you cannot enter a 16 digit *number*.
You
can only enter the value as text.

Since it is a pain to enter the text with the hyphens, I would suggest

1. Preformat the data entry fields as TEXT.
2. Enter your 16 digit number.
3. Run this UDF
=InsHyphens(cell_ref)

To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste
the
code below into the window that opens:

==========================================
Option Explicit
Function InsHyphens(str As String)
Dim i As Long
Dim Temp(15)
Dim s As String

For i = 0 To 15
s = Mid(str, i + 1, 1)
If IsNumeric(s) Then
Temp(i) = CLng(s)
Else
InsHyphens = CVErr(xlErrValue)
Exit Function
End If
Next i

InsHyphens = Join(Temp, "-")
End Function
========================================

As written, the code will give valid output if your string contains 16 or
more
digits. It will give a #VALUE! error if you have fewer than 16 digits, or
if
any character of the first 16 is not a digit.


You don't have to use a UDF, you could write a formula of the type:

=LEFT(A1,1)&"-"&
MID(A1,2,1)&"-" &
MID(A1,3,1) & "-" &
...

and so forth until you have handled all 16 digits.

It was easier for me to write the UDF.

--ron