Excel Simple Number Formatting

[Guest]

Would like to create a simple cell with 16 numeric characters which would be
layed out in the format of 1234 5678 9012 3456. When attempt to do this
either unable to achieve spacing or the application exponentiates as more
than 12 characters resulting in the last digit being rounded when it is
actually required to display as it was keyed.

Appreciate any suggestions, have spent considerable time trying to resolve
but so far to no avail

 

[Dave Peterson]

Excel keeps track of 15 significant digits. After 15, the rest turn to 0's.

You can enter the data as text by...

Preformatting the cell as text
starting the entry with an apostrophe ('1234123412341234)

Saved from another post:

Type this in A1:
'1234123412341234

and use this in that helper column:
=mid(a1,1,4)&" "&mid(a1,5,4)&" "&mid(a1,9,4)&" "&mid(a1,13,4)

or you could use a worksheet event that does the work for you.

If you want to try this idea, rightclick on the worksheet tab that should have
this behavior. Select view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant

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

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

errhandler:
Application.EnableEvents = True

End Sub

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

But make sure whatever range you use is preformatted to text. If you leave it
general, then that 16th digit is already a 0 when the code starts.

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