Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format
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) "littlesword" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format
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. -- David Biddulph "Bob Phillips" wrote in message ... 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) "littlesword" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format
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. "littlesword" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format
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: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 "littlesword" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert numbers from text format to number format | Excel Discussion (Misc queries) | |||
how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) |