How do we get the last digit to hold

Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

--
Regards
Frank Kabel
Frankfurt, Germany

"ceking" schrieb im Newsbeitrag
...
How do we get the last digit to hold

Hi Frank

I was looking at the same thing yesterday by coincidence and came up with
same answer as you have given.

Is there anyway in Custom format you can Enter the sixteen numbers with a
space/hyphen between each set of four?

I've tried various options with no success.

Thanks

Steve
"Frank Kabel" wrote in message
...
Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

--
Regards
Frank Kabel
Frankfurt, Germany

"ceking" schrieb im Newsbeitrag
...
How do we get the last digit to hold

Hi
no there isn't. as Excel allows only 15 significant digits for a
numeric value you can't have a custom number format with 16 digits. So
this would require VBA (using an event procedure)

--
Regards
Frank Kabel
Frankfurt, Germany

"Steve Jones" schrieb im Newsbeitrag
...
Hi Frank

I was looking at the same thing yesterday by coincidence and came up
with
same answer as you have given.

Is there anyway in Custom format you can Enter the sixteen numbers
with a
space/hyphen between each set of four?

I've tried various options with no success.

Thanks

Steve
"Frank Kabel" wrote in message
...
Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

--
Regards
Frank Kabel
Frankfurt, Germany

"ceking" schrieb im Newsbeitrag
...
How do we get the last digit to hold

This works against column A. But you could change it for any range.

But that range must be formatted as text (or precede every entry with an
apostrophe).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant
Dim myStr As String

On Error GoTo errhandler:

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub

myStr = Right(String(16, "0") & Target.Value, 16)

myTempVal = CDec(myStr)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "0000\-0000\-0000\-0000")

errhandler:
Application.EnableEvents = True

End Sub

Right click on the worksheet tab that should have this behavior and select view
code. Paste this in that code window.

I used all of column A in this line:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
but you could use:
If Intersect(Target, Me.Range("c7:g99")) Is Nothing Then Exit Sub

And I used dashes in this line:
Target.Value = Format(myTempVal, "0000\-0000\-0000\-0000")
but you could use:
Target.Value = Format(myTempVal, "0000 0000 0000 0000")



Steve Jones wrote:

Hi Frank

I was looking at the same thing yesterday by coincidence and came up with
same answer as you have given.

Is there anyway in Custom format you can Enter the sixteen numbers with a
space/hyphen between each set of four?

I've tried various options with no success.

Thanks

Steve
"Frank Kabel" wrote in message
...
Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

--
Regards
Frank Kabel
Frankfurt, Germany

"ceking" schrieb im Newsbeitrag
...
How do we get the last digit to hold



--

Dave Peterson

And a nice reference...

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Steve Jones wrote:

Hi Frank

I was looking at the same thing yesterday by coincidence and came up with
same answer as you have given.

Is there anyway in Custom format you can Enter the sixteen numbers with a
space/hyphen between each set of four?

I've tried various options with no success.

Thanks

Steve
"Frank Kabel" wrote in message
...
Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

--
Regards
Frank Kabel
Frankfurt, Germany

"ceking" schrieb im Newsbeitrag
...
How do we get the last digit to hold



--

Dave Peterson