data gets rounded up !!!

[Dr Alok Modi MD]

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]

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.

 

[AKphidelt]

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

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

 

[Teethless mama]

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

 

[AKphidelt]

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

 

[David McRitchie]

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.

 

[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





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

 

[Roger Govier]

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.

 

[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

 

[Gord Dibben]

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

 

[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

 

[Gord Dibben]

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

 

[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

 

[hb]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

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

 

[Dr Alok Modi MD]

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

 

[Ron Rosenfeld]

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

 

[Dr Alok Modi MD]

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.

 

[Gord Dibben]

Let's go back to the beginning.

The data "comes from the credit card machine".

How is this data retrieved? Imported through Data>Get External Data?


Gord