large numbers as text replaced with scientific notation

[hillearyburgess]

I did a search-and-replace to remove spaces from a column of 16-digit
credit card numbers stored in cells formatted as text, but afterwards,
the numbers appeared as scientific notation. If I then select each
cell in turn and click in the formula bar, the scientific notation
goes away, but the last digit of each string is replaced with zero.
How can I use search-and-replace to remove spaces without altering the
strings of digits or truning them into scientific notation?

 

[RagDyer]

You could use a text formula.

With column of numbers starting in A1, insert a "helper" column, and enter
this:

=SUBSTITUTE(A1," ","")

Copy down as needed.

Then, to remove the formulas, and leave just the data behind,
select the helper column, and Right click in the selection and choose
"Copy",

Right click again and choose :"Paste Special",
Click on "Values",
Then <OK>, then <Esc>,
and you now have a column of data.

You could delete the original column if desired.

 

[JBeaucaire]

Use Substitute in an adjacent cell first to trim out the spaces.

=SUBSTITUTE(F2," ","")

Now copy that down, highlight then entire range of cells, COPY, and EDIT >
PASTE SPECIAL > Values over the original credit card numbers. Then delete the
helper cells.