Format cell to Number

[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"

 

[Dave Peterson]

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

 

[Bob I]

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.

 

[David R. S.]

Thanks Dave Peterson.

Worked like a charm - text format

David

 

[dinosaur]

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.

;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:-

 

[David Biddulph]

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

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.

;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"-

 

[kwdight]

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?

 

[Dave Peterson]

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.)

 

[Ron Rosenfeld]

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

Dave,

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

--ron
--ron

 

[Ron Rosenfeld]

Dave,

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

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

 

[Dave Peterson]

I may not have made it clear, but the entry has to made as text in both the
worksheet formula and the event macro.

 

[Dave Peterson]

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).

 

[Ron Rosenfeld]

I may not have made it clear, but the entry has to made as text in both the
worksheet formula and the event macro.

Ah, that would do it. I didn't see that caveat.
--ron

 

[Ron Rosenfeld]

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.

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).

That works too, if the formatting can be guaranteed.
--ron

 

[Ron Rosenfeld]

Ah, that would do it. I didn't see that caveat.
--ron

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