Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
large numbers as text replaced with scientific notation
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
large numbers as text replaced with scientific notation
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
large numbers as text replaced with scientific notation
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. -- "Actually, I AM a rocket scientist." -- JB " wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
show value as text not scientific notation | Excel Discussion (Misc queries) | |||
Hex numbers displayed in scientific notation when importing text f | Excel Discussion (Misc queries) | |||
Scientific notation in text cells | Excel Discussion (Misc queries) | |||
Using scientific notation in TEXT() function | Excel Worksheet Functions | |||
Long numbers show up as Scientific Notation | Excel Discussion (Misc queries) |