Format cell to Number

  • Thread starter Thread starter David R. S.
  • Start date Start date
D

David R. S.

I have formatted a excel 2007 cell as a number. I enter a 16 number credit
card. The last 4 numbers are:
6839

The cell automaticall changes the last 4 numbers to:
6830

When I press return or the tab key.

How Do I get the cell to not change the last number to a "0"
 
Excel only keeps track of 15 significant digits.

You can preformat the cell as text, then do the data entry.
or
You can prefix your entry with an apostrophe: '1234123412341234
 
Put an apostrophe in front of the "number" OR format the cell as Text
before you plug in the "number". A "real" number can only have 15
significant digits, so you need to enter it as text.
 
Hi! What if I want to automatically insert spaces or hyphens every after
four numbers?

I also have 16 numbers, have tried several formattings but always gets
the last number as 0.

Any ideas?

Thanks.


'David R. S.[_2_ said:
;627171']Thanks Dave Peterson.

Worked like a charm - text format

David

:
-
Excel only keeps track of 15 significant digits.

You can preformat the cell as text, then do the data entry.
or
You can prefix your entry with an apostrophe: '1234123412341234

David R. S. wrote:-
Click to expand...
Click to expand...
 
Read again the reply below. Excel numbers work to 15 significant figures.
If you want 16 or more it'll need to be inserted as text, either by
formatting the cell as text *before* you type the number in, or by preceding
the text by an apostrophe. If you're always seeing the last digit as a
zero, you haven't inserted it as text.
--
David Biddulph

dinosaur said:
Hi! What if I want to automatically insert spaces or hyphens every after
four numbers?

I also have 16 numbers, have tried several formattings but always gets
the last number as 0.

Any ideas?

Thanks.


'David R. S.[_2_ said:
;627171']Thanks Dave Peterson.

Worked like a charm - text format

David

:
-
Excel only keeps track of 15 significant digits.

You can preformat the cell as text, then do the data entry.
or
You can prefix your entry with an apostrophe: '1234123412341234

David R. S. wrote:-

I have formatted a excel 2007 cell as a number. I enter a 16 number credit
card. The last 4 numbers a
6839

The cell automaticall changes the last 4 numbers to:
6830

When I press return or the tab key.

How Do I get the cell to not change the last number to a "0"-
Click to expand...
Click to expand...
Click to expand...
 
I, also, have a field set up for credit cards that acts the same way. I,
however, have been using a Custom Template (####-####-####-####) so that I
only have to enter the numbers and not the hyphens. Is there some way of
doing this so that Excel sees it as text and therefore the hyphens are
automatically inserted?
 
If you type the value as text (either pre-format the cell or text and then do
the data entry or start your entry with an apostrophe), then you can use this
technique:

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


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
If you type the value as text (either pre-format the cell or text and then do
the data entry or start your entry with an apostrophe), then you can use this
technique:

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
Click to expand...

Dave,

The worksheet event routine you posted will still drop the 16th digit (turn it
into a zero).

--ron
--ron
 
Dave,

The worksheet event routine you posted will still drop the 16th digit (turn it
into a zero).
Click to expand...

I think if you did two events == first a selection change to make the
formatting "@" (text); then a worksheet change to test for an appropriate entry
and format it appropriately in VBA.

Something like this (without the tests for correct entry or error checking):
=======================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Cells.NumberFormat <> "@" Then Exit Sub
Target.Value = Format(Target.Value, "0000-0000-0000-0000")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
Target.NumberFormat = "@"
End Sub
==================================
--ron
 
I may not have made it clear, but the entry has to made as text in both the
worksheet formula and the event macro.
 
If you really wanted to use the worksheet_selectionchange event, then I would
think that you'd want to change the format of any cells in that input range to
text--no matter how many cells were selected -- and I don't think I'd change the
format of cells outside that range.

But I think I would just preformat the input range as text first (all of column
A in both our samples).
 
I may not have made it clear, but the entry has to made as text in both the
worksheet formula and the event macro.
Click to expand...

Ah, that would do it. I didn't see that caveat.
--ron
 
If you really wanted to use the worksheet_selectionchange event, then I would
think that you'd want to change the format of any cells in that input range to
text--no matter how many cells were selected -- and I don't think I'd change the
format of cells outside that range.
Click to expand...

That's also a good approach.
But I think I would just preformat the input range as text first (all of column
A in both our samples).
Click to expand...

That works too, if the formatting can be guaranteed.
--ron
 
Ah, that would do it. I didn't see that caveat.
--ron
Click to expand...

To amplify, I saw the caveat for the formula/helper-column approach, but didn't
realize that it should also apply to the worksheet-event approach.
--ron
 
Back
Top