Credit Card type numbers

  • Thread starter Thread starter Z
  • Start date Start date
Z

Z

Because excel will truncate 16 digit numbers, we want to
use text formatting instead which will leave the number
intact. However, it is easier to read when the groups of
four digit numbers have dashes in between. 111-222-333-
4444
Ts there a way to instruct excel to insert dashes after
every fourth text symbol as in credit card numbers?
 
You would have to provide your own subroutine or function there
is nothing in Excel to format text as you can with numbers.

I take it that from whatever the source that you have digits and
only without hyphens or spaces within. So you may have to
maintain an original and a formatted version anyway. So
according to your description and not your example.

=IF(LEN(A1)<>15,A1,left(a1,4)&"-"&mid(a1,5,4)&"-"&mid(a1,9,4)&"-"&right(a1,4) )

Good that you realize Excel only does 15 significant digits before
you destroyed your data.
 
Hi
unfortunately credit card numbers have 16 digits. Excel supports only
15 digits. So a custom number format won't work.
You may use a helper column. Say you enter the credit card number as
'Text' in cell A1 (without dashes) use the following formula in B1
=LEFT(A1,4) & "-" & MID(A1,5,4) & "-" & MID(A1,9,5) & "-" & RIGHT(A1,4)
 
One way to accomplish this without using code would be to perhaps set up 2
column in an out-of-the-way portion of your WB, where one would receive the
unformatted text digits and the other would contain the format converting
text formula.
You could then copy *only* the data from this formula column to the regular
column in your original form.

Assume 16 digit text numbers are in column Y.
Enter this in column Z:

=IF(Y1<>"",LEFT(Y1,4) & "-" & MID(Y1,5,4) & "-" & MID(Y1,9,4) & "-" &
RIGHT(Y1,4),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Because excel will truncate 16 digit numbers, we want to
use text formatting instead which will leave the number
intact. However, it is easier to read when the groups of
four digit numbers have dashes in between. 111-222-333-
4444
Ts there a way to instruct excel to insert dashes after
every fourth text symbol as in credit card numbers?
 
Typo in the second MID Frank.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi
unfortunately credit card numbers have 16 digits. Excel supports only
15 digits. So a custom number format won't work.
You may use a helper column. Say you enter the credit card number as
'Text' in cell A1 (without dashes) use the following formula in B1
=LEFT(A1,4) & "-" & MID(A1,5,4) & "-" & MID(A1,9,5) & "-" & RIGHT(A1,4)
 
thanks
for the OP:
=LEFT(A1,4) & "-" & MID(A1,5,4) & "-" & MID(A1,9,4) & "-" & RIGHT(A1,4)
 
Back
Top