Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default end number keeps changing to '0'

Hello,

I am trying to keep trackof credit card numbers, i put in a custom formating
that looks like this: ####-####-####-#### so i can just type the numbers in
without having to type '-'.

The only problem is that is always turns the last number into a '0' zero.

for instance if the number is:
4519-3589-2000-1875

the formula changes the number to this:
4519-3589-2000-1870

Thank you,

shivaraj


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default end number keeps changing to '0'

Excel only handles 15 digits so for Credit Cards (and long telephone
numbers!) you should format your cells as TEXT.

"Shivaraj" wrote:

Hello,

I am trying to keep trackof credit card numbers, i put in a custom formating
that looks like this: ####-####-####-#### so i can just type the numbers in
without having to type '-'.

The only problem is that is always turns the last number into a '0' zero.

for instance if the number is:
4519-3589-2000-1875

the formula changes the number to this:
4519-3589-2000-1870

Thank you,

shivaraj


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default end number keeps changing to '0'

When I format it as text it gives me, 5.4575E+15
I can put a ['] in fron of the numbers and it will work. but is there a way
for me to just type in the numbers and have it format like this:
4525-4545-4585-5656

thank you

"Toppers" wrote:

Excel only handles 15 digits so for Credit Cards (and long telephone
numbers!) you should format your cells as TEXT.

"Shivaraj" wrote:

Hello,

I am trying to keep trackof credit card numbers, i put in a custom formating
that looks like this: ####-####-####-#### so i can just type the numbers in
without having to type '-'.

The only problem is that is always turns the last number into a '0' zero.

for instance if the number is:
4519-3589-2000-1875

the formula changes the number to this:
4519-3589-2000-1870

Thank you,

shivaraj


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default end number keeps changing to '0'

When I format it as text it gives me, 5.4575E+15
I can put a ['] in fron of the numbers and it will work. but is there a way
for me to just type in the numbers and have it format like this:
4525-4545-4585-5656

"Toppers" wrote:

Excel only handles 15 digits so for Credit Cards (and long telephone
numbers!) you should format your cells as TEXT.

"Shivaraj" wrote:

Hello,

I am trying to keep trackof credit card numbers, i put in a custom formating
that looks like this: ####-####-####-#### so i can just type the numbers in
without having to type '-'.

The only problem is that is always turns the last number into a '0' zero.

for instance if the number is:
4519-3589-2000-1875

the formula changes the number to this:
4519-3589-2000-1870

Thank you,

shivaraj


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default end number keeps changing to '0'

The only solution I havefound is to:

Format cells as text and then enter the data (ay in Column A)

In column B put =TEXT(A1,"0000-0000-0000-0000") and copy down

At some point. copy/paste special=values

OR

Place this code in worksheet (right click on tab, view code and copy and
paste)

Change Range("A:A") to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo wsexit:

Set isect = Application.Intersect(Range("A:A"), Target)
If isect Is Nothing Then
' MsgBox "Ranges do not intersect"
Else
Target.Value = Format(Target.Value, "0000-0000-0000-0000")
End If
wsexit:
Application.EnableEvents = True
End Sub




"Shivaraj" wrote:

Hello,

I am trying to keep trackof credit card numbers, i put in a custom formating
that looks like this: ####-####-####-#### so i can just type the numbers in
without having to type '-'.

The only problem is that is always turns the last number into a '0' zero.

for instance if the number is:
4519-3589-2000-1875

the formula changes the number to this:
4519-3589-2000-1870

Thank you,

shivaraj


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
number changing digits on paste from web apx file field Todd F. Excel Discussion (Misc queries) 0 March 15th 06 04:49 PM
How to keep "text" number from changing to date.... Julie Willems New Users to Excel 2 January 17th 06 07:40 PM
Changing format of number without changing the value sweetsue516 Excel Discussion (Misc queries) 2 August 22nd 05 04:07 PM
edit number in cell without it changing to text rklremote Excel Discussion (Misc queries) 1 May 19th 05 10:22 PM
Changing a formulas result to be a static number Desperate Excel Discussion (Misc queries) 3 May 7th 05 02:25 AM


All times are GMT +1. The time now is 10:24 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"