Format cell to Number
 

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"

Format cell to Number
 

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"

--

Dave Peterson

Format cell to Number
 

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

Format cell to Number
 

Thanks Dave Peterson.

Worked like a charm - text format

David

"Dave Peterson" wrote:

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"

--

Dave Peterson

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.

Format cell to Number
 

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" wrote in message
...

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_ Wrote:
;627171']Thanks Dave Peterson.

Worked like a charm - text format

David

"Dave Peterson" wrote:
-
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"-

--

Dave Peterson
-




--
dinosaur

Format cell to Number
 

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?
--
Kenneth Dight


"Bob I" wrote:

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

Format cell to Number
 

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

kwdight wrote:

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?
--
Kenneth Dight

"Bob I" wrote:

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




--

Dave Peterson

Format cell to Number
 

On Mon, 17 Aug 2009 06:51:12 -0500, Dave Peterson
wrote:

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

Format cell to Number
 

On Mon, 17 Aug 2009 08:40:46 -0400, Ron Rosenfeld
wrote:

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

Format cell to Number
 

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



Ron Rosenfeld wrote:

Dave,

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

--ron
--ron

--

Dave Peterson

Format cell to Number
 

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

On Mon, 17 Aug 2009 08:40:46 -0400, Ron Rosenfeld
wrote:

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

Format cell to Number
 

On Mon, 17 Aug 2009 09:15:20 -0500, Dave Peterson
wrote:

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

Format cell to Number
 

On Mon, 17 Aug 2009 09:18:43 -0500, Dave Peterson
wrote:

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

Format cell to Number
 

On Mon, 17 Aug 2009 10:43:42 -0400, Ron Rosenfeld
wrote:

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