Number format

[littlesword]

I want to format a cell wherein spaces will be automatically added to the
numbers that I will type. The format should be like this 1111 2222 3333 4444.
I was able to make this format but the problem is, it always replace my last
number to 0. For example, if I type 5462 5700 0123 4568 it would be displayed
as 5462 5700 0123 4568. The custom format I used was #### #### #### ####.
What should I change with this format?

 

[Bob Phillips]

That is because Excel number precision only works to 15 digits. That last
one will get truncated whether you use a custom format or not.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[David Biddulph]

Hence if you need to show more than 15 significant figures you'll need to
format the cell as text before you type in your number, or type an
apostrophe before the number. You won't, of course, be able to do
arithmetic with the "number", but if it is a credit card number you won't
need to.

 

[Stephen]

You cannot do this. Excel will only accept a number of up to 15 significant
digits, so any more will become zeros.

You can display more characters by formatting the cell as text before
entering the data, but then you cannot customise the format; you would need
to type in the spaces as you go.

The only other alternative I can think of is to type in 16 characters as
text, and have a formula in an adjacent cell to add the spaces for you.

 

[Rick Rothstein \(MVP - VB\)]

If you are up for a macro solution, then you might want to consider this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("D")) Is Nothing Then
Target.NumberFormat = "@"
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Entry As String
On Error GoTo Whoops
Entry = Replace(Target.Text, " ", "")
Application.EnableEvents = False
If Not Entry Like "*[!0-9]*" And Len(Entry) = 16 Then
Target.Value = Format$(Entry, "@@@@ @@@@ @@@@ @@@@")
ElseIf Not Entry Like "*[!0-9]*" Then
Target.NumberFormat = "General"
Target.Value = CDbl(Target.Value)
End If
Whoops:
Application.EnableEvents = True
End Sub

It lets you type in your number with or without the separating spaces. If
your number is not 16 digits long or if a non-number is entered, then the
entry is left as is; however, if your entry has 16 digits (with or without
spaces), then it will be formatted in groups of 4. As written for this
example, only values in Column D will be processed... change the Range shown
in the Intersect method to those columns or cells you want your spreadsheet
to process.

Rick