Formatting long numbers accurately

[EMR]

I am working with credit card #s. As soon as I take out
(Using Replace) the dashes, the last digit becomes a
zero! I've tried everything, it's urgent.
example: 1234-5476-7894-1236 to 1234547678941236

 

[Peo Sjoblom]

Format as text

 

[EMR]

-----Original Message-----
Format as text

--

Regards,

Peo Sjoblom




.
I tried that, as long as I'm less than 16 digits, it's

fine - but even as text, it changes the last digit to 0.

 

[Ron Rosenfeld]

I am working with credit card #s. As soon as I take out
(Using Replace) the dashes, the last digit becomes a
zero! I've tried everything, it's urgent.
example: 1234-5476-7894-1236 to 1234547678941236

Instead of using Replace, do the following:

Insert an extra column next to your list of CC#'s

Use this formula: =SUBSTITUTE(A1,"-","") and copy/drag down as needed.

This will retain the text characteristic of the original number.


--ron

 

[Jerry W. Lewis]

Not sure if earlier versions did it differently, but Excel 2000 and
Excel XP both coerce text to number if possible on a search & replace.

Instead, enter as '1234-5678-9012-3456 to designate text. The single
quote will not show, but will prevent Excel from converting to a number.

Jerry

 

[David McRitchie]

Try again. Format as text BEFORE you enter your credit card ID.
Formatting as text is not going to make missing digit come back,
as Excel only provides for 15 digits of precision. Also simply
changing the format between numbers and text; or between text
and numbers does nothing until you force reentry in some manner.

If you format a number to a different number format the change is
immediate. i.e. a number formatted to a date and time format
is immediate. Formatting to include commas or dollar signs is
immediate.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Peo Sjoblom]

Shouldn't it be?

'1234567890123456

 

[Jerry W. Lewis]

The OP was wanting the form with dashes to survive a search and and
replace to remove the dashes and still be text. If the dashes were not
required at some point in the process, the entire problem goes away.

Jerry