Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ceking
 
Posts: n/a
Default When we enter a 16 digit number (credit card) the last digit chan.

How do we get the last digit to hold

  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

--
Regards
Frank Kabel
Frankfurt, Germany

"ceking" schrieb im Newsbeitrag
...
How do we get the last digit to hold


  #3   Report Post  
Steve Jones
 
Posts: n/a
Default

Hi Frank

I was looking at the same thing yesterday by coincidence and came up with
same answer as you have given.

Is there anyway in Custom format you can Enter the sixteen numbers with a
space/hyphen between each set of four?

I've tried various options with no success.

Thanks

Steve
"Frank Kabel" wrote in message
...
Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

--
Regards
Frank Kabel
Frankfurt, Germany

"ceking" schrieb im Newsbeitrag
...
How do we get the last digit to hold




  #4   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
no there isn't. as Excel allows only 15 significant digits for a
numeric value you can't have a custom number format with 16 digits. So
this would require VBA (using an event procedure)

--
Regards
Frank Kabel
Frankfurt, Germany

"Steve Jones" schrieb im Newsbeitrag
...
Hi Frank

I was looking at the same thing yesterday by coincidence and came up

with
same answer as you have given.

Is there anyway in Custom format you can Enter the sixteen numbers

with a
space/hyphen between each set of four?

I've tried various options with no success.

Thanks

Steve
"Frank Kabel" wrote in message
...
Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

--
Regards
Frank Kabel
Frankfurt, Germany

"ceking" schrieb im Newsbeitrag
...
How do we get the last digit to hold





  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

This works against column A. But you could change it for any range.

But that range must be formatted as text (or precede every entry with an
apostrophe).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant
Dim myStr As String

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

myStr = Right(String(16, "0") & Target.Value, 16)

myTempVal = CDec(myStr)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "0000\-0000\-0000\-0000")

errhandler:
Application.EnableEvents = True

End Sub

Right click on the worksheet tab that should have this behavior and select view
code. Paste this in that code window.

I used all of column A in this line:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
but you could use:
If Intersect(Target, Me.Range("c7:g99")) Is Nothing Then Exit Sub

And I used dashes in this line:
Target.Value = Format(myTempVal, "0000\-0000\-0000\-0000")
but you could use:
Target.Value = Format(myTempVal, "0000 0000 0000 0000")



Steve Jones wrote:

Hi Frank

I was looking at the same thing yesterday by coincidence and came up with
same answer as you have given.

Is there anyway in Custom format you can Enter the sixteen numbers with a
space/hyphen between each set of four?

I've tried various options with no success.

Thanks

Steve
"Frank Kabel" wrote in message
...
Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

--
Regards
Frank Kabel
Frankfurt, Germany

"ceking" schrieb im Newsbeitrag
...
How do we get the last digit to hold



--

Dave Peterson


  #6   Report Post  
Dave Peterson
 
Posts: n/a
Default

And a nice reference...

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Steve Jones wrote:

Hi Frank

I was looking at the same thing yesterday by coincidence and came up with
same answer as you have given.

Is there anyway in Custom format you can Enter the sixteen numbers with a
space/hyphen between each set of four?

I've tried various options with no success.

Thanks

Steve
"Frank Kabel" wrote in message
...
Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

--
Regards
Frank Kabel
Frankfurt, Germany

"ceking" schrieb im Newsbeitrag
...
How do we get the last digit to hold



--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
You Must Enter A Number In This Cell la90292 Excel Discussion (Misc queries) 3 December 4th 04 08:46 PM


All times are GMT +1. The time now is 12:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"