I have devised a workbook where I enter the credit card details of the
transactions received at my hospital for people who pay by credit card. In
this there is one column where I enter the credit card number. This cell I
have given a general format, text format and a number format. But my tragedy
is that the last digit gets automatically rounded up. I don't understand
this. What do I do ?

Thanks

Dr Alok Modi MD

Hi

A number can only have 15 digits in Excel.
Precede the entry with a single quote to force it to be text, or
pre-format the cells as text BEFORE entering the CC number.

--
Regards

Roger Govier


"Dr Alok Modi MD" wrote in
message ...
I have devised a workbook where I enter the credit card details of the
transactions received at my hospital for people who pay by credit
card. In
this there is one column where I enter the credit card number. This
cell I
have given a general format, text format and a number format. But my
tragedy
is that the last digit gets automatically rounded up. I don't
understand
this. What do I do ?

Thanks

Dr Alok Modi MD

Try creating a custom number format... if it's always 12 digit credit card
numbers try

####-####-####-####

or if you don't want the dashes just type # 12 times...

############


"Dr Alok Modi MD" wrote:

I have devised a workbook where I enter the credit card details of the
transactions received at my hospital for people who pay by credit card. In
this there is one column where I enter the credit card number. This cell I
have given a general format, text format and a number format. But my tragedy
is that the last digit gets automatically rounded up. I don't understand
this. What do I do ?

Thanks

Dr Alok Modi MD

Pre-format cells as Text, or precede with aspostrophe.


"Dr Alok Modi MD" wrote:

I have devised a workbook where I enter the credit card details of the
transactions received at my hospital for people who pay by credit card. In
this there is one column where I enter the credit card number. This cell I
have given a general format, text format and a number format. But my tragedy
is that the last digit gets automatically rounded up. I don't understand
this. What do I do ?

Thanks

Dr Alok Modi MD

whoops, i forgot 4 * 4 = 16... should still work though

"AKphidelt" wrote:

Try creating a custom number format... if it's always 12 digit credit card
numbers try

####-####-####-####

or if you don't want the dashes just type # 12 times...

############


"Dr Alok Modi MD" wrote:

I have devised a workbook where I enter the credit card details of the
transactions received at my hospital for people who pay by credit card. In
this there is one column where I enter the credit card number. This cell I
have given a general format, text format and a number format. But my tragedy
is that the last digit gets automatically rounded up. I don't understand
this. What do I do ?

Thanks

Dr Alok Modi MD

No sixteen digits is one more digit than 15, and
15 is the maximum in digits if Excel. You cannot
use a number format, and will have to enter
the identification as a text entry as previously
stated.


"AKphidelt" wrote in message ...
whoops, i forgot 4 * 4 = 16... should still work though

"AKphidelt" wrote:

Try creating a custom number format... if it's always 12 digit credit card
numbers try

####-####-####-####

or if you don't want the dashes just type # 12 times...

############


"Dr Alok Modi MD" wrote:

I have devised a workbook where I enter the credit card details of the
transactions received at my hospital for people who pay by credit card. In
this there is one column where I enter the credit card number. This cell I
have given a general format, text format and a number format. But my tragedy
is that the last digit gets automatically rounded up. I don't understand
this. What do I do ?

Thanks

Dr Alok Modi MD

"Roger Govier" wrote:

Hi

A number can only have 15 digits in Excel.
Precede the entry with a single quote to force it to be text, or
pre-format the cells as text BEFORE entering the CC number.

--
Regards

Roger Govier


"Dr Alok Modi MD" wrote in
message ...
I have devised a workbook where I enter the credit card details of the
transactions received at my hospital for people who pay by credit
card. In
this there is one column where I enter the credit card number. This
cell I
have given a general format, text format and a number format. But my
tragedy
is that the last digit gets automatically rounded up. I don't
understand
this. What do I do ?

Thanks

Dr Alok Modi MD


Thanks all of you, I did custom format as you have mentioned, I did text format, nothing works. Don't laugh at me when I say that text format does not work, Can I upload the file so you guys can see for yourself ?

Thanks
Dr Alok Modi MD

Hi

Applying a format after you have entered data, will not change the data
in the cell. You would need to re-enter the values.

If you format a cell as Text, then enter 01234567890123456 it will take
all 16 digits and will retain leading zeros.

--
Regards

Roger Govier


"Dr Alok Modi MD" wrote in
message ...


"Roger Govier" wrote:

Hi

A number can only have 15 digits in Excel.
Precede the entry with a single quote to force it to be text, or
pre-format the cells as text BEFORE entering the CC number.

--
Regards

Roger Govier


"Dr Alok Modi MD" wrote in
message ...
I have devised a workbook where I enter the credit card details of
the
transactions received at my hospital for people who pay by credit
card. In
this there is one column where I enter the credit card number. This
cell I
have given a general format, text format and a number format. But
my
tragedy
is that the last digit gets automatically rounded up. I don't
understand
this. What do I do ?

Thanks

Dr Alok Modi MD


Thanks all of you, I did custom format as you have mentioned, I did
text format, nothing works. Don't laugh at me when I say that text
format does not work, Can I upload the file so you guys can see for
yourself ?

Thanks
Dr Alok Modi MD

Thanks but I have done all that. See for eg if I were to enter a credit card
number 1234 1234 1234 1234 it gets entered as 1234 1234 1234 1230. Even if
the credit card number were 1234 1234 1234 1237 it would still get entered as
1234 1234 1234 1230. Excel rounds the last digit as zero no matter if the
last digit of the credit card number that you enter is less than five or
greater than five. Please help me as my work has come to a standstill and I
can't work this problem out . I am sure this is a useful question as this
problem would be common to many other users of excel.


Dr Alok Modi MD

Not if you enter the number preceded by an apostrophe which Excel reads as
anything following is text

'1234 1234 1234 1234

Or pre-format the cell(s) as Text.


Gord Dibben MS Excel MVP

On Mon, 14 May 2007 19:28:39 -0700, Dr Alok Modi MD
wrote:



Thanks but I have done all that. See for eg if I were to enter a credit card
number 1234 1234 1234 1234 it gets entered as 1234 1234 1234 1230. Even if
the credit card number were 1234 1234 1234 1237 it would still get entered as
1234 1234 1234 1230. Excel rounds the last digit as zero no matter if the
last digit of the credit card number that you enter is less than five or
greater than five. Please help me as my work has come to a standstill and I
can't work this problem out . I am sure this is a useful question as this
problem would be common to many other users of excel.


Dr Alok Modi MD

Thanks Gord
That ! did the job. But fromatting the text as text converts the data as
something like this 12E + something.

Thanks
Dr Alok Modi MD

I cannot replicate your problem using a pre-formatted cell or by preceding with
an apostrophe.

I can if I enter the data then format to text

Do not re-format after you have typed in the text.

Pre-format prior to entering the text 1234 1234 1234 1234

If you are entering the spaces as above you don't even need to pre-format or add
the apostrophe


Gord

On Tue, 15 May 2007 19:48:02 -0700, Dr Alok Modi MD
wrote:

Thanks Gord
That ! did the job. But fromatting the text as text converts the data as
something like this 12E + something.

Thanks
Dr Alok Modi MD

Thanks a lot Gord. My problem got solved like you did. Even if I did enter
the data and then gave the format as text, I was getting the data appaering
as 12E+ something. So if I were to double click on that cell, it comes back
as 1234123412341234. Now only if we had some means of giving a format of
1234-1234-1234-1234, which is possible if your data type is numbers, but I am
not aware of this in text data type.
Dr Alok Modi MD

I'm stymied by this same problem. Is there no way to format a cell so that a
credit card number entered as a string of 16 digits will display as 4 groups
of four, with the groups separated by a hyphen or a space, in the same way a
cell can be formatted to display a string of 10 digits as (###) ###-####
(10-digit phone number)?

"Dr Alok Modi MD" wrote:

Thanks a lot Gord. My problem got solved like you did. Even if I did enter
the data and then gave the format as text, I was getting the data appaering
as 12E+ something. So if I were to double click on that cell, it comes back
as 1234123412341234. Now only if we had some means of giving a format of
1234-1234-1234-1234, which is possible if your data type is numbers, but I am
not aware of this in text data type.
Dr Alok Modi MD

On Thu, 11 Dec 2008 21:42:00 -0800, hb wrote:

I'm stymied by this same problem. Is there no way to format a cell so that a
credit card number entered as a string of 16 digits will display as 4 groups
of four, with the groups separated by a hyphen or a space, in the same way a
cell can be formatted to display a string of 10 digits as (###) ###-####
(10-digit phone number)?

Your number must be entered in a cell that has been previously formatted as
Text, or it must be preceded by a single quote.

It is certainly possible to run a VBA event triggered macro to format it the
way you want after you have entered it, if you didn't enter it in the correct
format.

Right click on the sheet tab. Select View Code and paste the code below into
the window that opens.

=============================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim rRng As Range
Dim sTemp As String

'set range to check for CC numbers
Set rRng = Range("a:a")

If Not Intersect(Target, rRng) Is Nothing Then
For Each c In Intersect(Target, rRng)
'remove <space and <hyphen
sTemp = Replace(c.Value, " ", "")
sTemp = Replace(sTemp, "-", "")
If Not Len(sTemp) = 16 Then
'output error message
'could have other checks here, too
c.Value = CVErr(xlErrValue)
Else
c.Value = Format(sTemp, "0000 0000 0000 0000")
End If
Next c
End If
End Sub
===============================
--ron

On Thu, 11 Dec 2008 21:42:00 -0800, hb wrote:

I'm stymied by this same problem. Is there no way to format a cell so that a
credit card number entered as a string of 16 digits will display as 4 groups
of four, with the groups separated by a hyphen or a space, in the same way a
cell can be formatted to display a string of 10 digits as (###) ###-####
(10-digit phone number)?

"Dr Alok Modi MD" wrote:

Thanks a lot Gord. My problem got solved like you did. Even if I did enter
the data and then gave the format as text, I was getting the data appaering
as 12E+ something. So if I were to double click on that cell, it comes back
as 1234123412341234. Now only if we had some means of giving a format of
1234-1234-1234-1234, which is possible if your data type is numbers, but I am
not aware of this in text data type.
Dr Alok Modi MD



Oops, minor change in code previously posted:

=========================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim rRng As Range
Dim sTemp As String

Set rRng = Range("a:a")
Application.EnableEvents = False

If Not Intersect(Target, rRng) Is Nothing Then
For Each c In Intersect(Target, rRng)
'remove <space and <hyphen
sTemp = Replace(c.Text, " ", "")
sTemp = Replace(sTemp, "-", "")
If Not Len(sTemp) = 16 Then
'output error message
'could have other checks here, too
c.Value = CVErr(xlErrValue)
Else
c.Value = Format(sTemp, "0000 0000 0000 0000")
End If
Next c
End If
Application.EnableEvents = True
End Sub
===============================
--ron

I was pleasantly surprised to see a response in my inbox after about 1 yr
that I posted this thread. Thanks Ron, for the code, I tried it, but it did
not work !!!
Dr Alok Modi MD

On Fri, 12 Dec 2008 15:16:08 -0800, Dr Alok Modi MD
wrote:

I was pleasantly surprised to see a response in my inbox after about 1 yr
that I posted this thread. Thanks Ron, for the code, I tried it, but it did
not work !!!
Dr Alok Modi MD

Then there is a difference between what I described and what you did, so you
will have to be more specific as to the precise steps you took; what you mean
by "did not work", etc.

--ron

Maybe I am wrong in what I have done, pl help me correct myself, if so. Like
you have posted, " Right click on the sheet tab. Select View Code and paste
the code below into the window that opens." I did that. I then pasted the
code in the 2nd post that you have written, " Oops, minor change in code
previously posted:"

I have a excel workbook which has 24 sheets. The purpose of this workbook is
to store the data of payments from patients who pay by credit card. Each
month carries data from the credit card machine of two banks, so there are
two sheets per month corresponding to two credit card machine each linked
with one bank. There is one column in each sheet which stores credit card
nos in text format. And like you can see in this thread that I had started 1
1/2 yrs back, Gord has mentioned how to enter tdata in text format. If you
permit,me, I would like to upload the workbook so you could tell me where I
am going wrong.

Let's go back to the beginning.

The data "comes from the credit card machine".

How is this data retrieved? Imported through DataGet External Data?


Gord

On Sat, 13 Dec 2008 18:14:01 -0800, Dr Alok Modi MD
wrote:


Maybe I am wrong in what I have done, pl help me correct myself, if so. Like
you have posted, " Right click on the sheet tab. Select View Code and paste
the code below into the window that opens." I did that. I then pasted the
code in the 2nd post that you have written, " Oops, minor change in code
previously posted:"

I have a excel workbook which has 24 sheets. The purpose of this workbook is
to store the data of payments from patients who pay by credit card. Each
month carries data from the credit card machine of two banks, so there are
two sheets per month corresponding to two credit card machine each linked
with one bank. There is one column in each sheet which stores credit card
nos in text format. And like you can see in this thread that I had started 1
1/2 yrs back, Gord has mentioned how to enter tdata in text format. If you
permit,me, I would like to upload the workbook so you could tell me where I
am going wrong.

No Gord, we manually enter the credit card number.

Dr Alok Modi MD

And you want to simply enter as 1234123412341234 and have formatted as

1234 1234 1234 1234 or some other format?


Gord

On Sun, 14 Dec 2008 17:39:05 -0800, Dr Alok Modi MD
wrote:


No Gord, we manually enter the credit card number.

Dr Alok Modi MD

On Sat, 13 Dec 2008 18:14:01 -0800, Dr Alok Modi MD
wrote:


Maybe I am wrong in what I have done, pl help me correct myself, if so. Like
you have posted, " Right click on the sheet tab. Select View Code and paste
the code below into the window that opens." I did that. I then pasted the
code in the 2nd post that you have written, " Oops, minor change in code
previously posted:"

I have a excel workbook which has 24 sheets. The purpose of this workbook is
to store the data of payments from patients who pay by credit card. Each
month carries data from the credit card machine of two banks, so there are
two sheets per month corresponding to two credit card machine each linked
with one bank. There is one column in each sheet which stores credit card
nos in text format. And like you can see in this thread that I had started 1
1/2 yrs back, Gord has mentioned how to enter tdata in text format. If you
permit,me, I would like to upload the workbook so you could tell me where I
am going wrong.

OK. As posted, the code will only work on the particular worksheet where you
have entered the data.

From your description, it now seems as if you have multiple worksheets which
need to behave the same way.

Probably you would be better off with a Workbook SheetChange event.

To enter that, after you right click on the sheet tab and select view code,
examine the Project Explorer window. You will find your particular "project"
named by workbook name. Highlight This Workbook within that project and
"double-click". A code window will open and you can paste the code below into
that window.

===========================================
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim c As Range
Dim rRng As Range
Dim sTemp As String

Set rRng = Range("a:a")
Application.EnableEvents = False

If Not Intersect(Target, rRng) Is Nothing Then
For Each c In Intersect(Target, rRng)
'remove <space and <hyphen
sTemp = Replace(c.Text, " ", "")
sTemp = Replace(sTemp, "-", "")
If Not Len(sTemp) = 16 Then
'output error message
'could have other checks here, too
c.Value = CVErr(xlErrValue)
Else
c.Value = Format(sTemp, "0000 0000 0000 0000")
End If
Next c
End If
Application.EnableEvents = True
End Sub
======================================

If the target column is not formatted as Text, you may get a VALUE error. You
may want to avoid having to do this manually by using a Worksheet SheetActivate
event:

===========================
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A:A").NumberFormat = "@"
End Sub
===============================
--ron