"Paul Lautman" wrote:

Jeff wrote:
"Paul Lautman" wrote:

Jeff wrote:
"Paul Lautman" wrote:
Thanks for youer resonse Paul and you are correct I am pasting one
column of cells to anopther column already formatted as text and
excel is intpreting for itself, however your statement above "To
avoid this use Edit-Paste Special... and select Text. Then it will
stay as you wish. Well this doesnt work because there is no option
to choose paste special as text the options are ALL, FORMATS,
VALUES, COMMENTS,VALIDATION, ALL EXCEPT BOARDERS,COLUMN
WIDTHS,FORMULAS AND NUMBER FORMATS, VALUES AND NUMBER FORMATS. I
tried them all with no luck.

Is there some kind of nested IF statement I could use that might
work? Like =If the contents of A1 ends with "," if true substitute
the "," with "" and if Flase duplicate A1 (exactly with all other
commas in place)
The options for Paste Special... are dependant on where you copied
the date
from.
If you are copying from Excel back into Excel choose VALUES. Indeed
I use
this so often I have added the Paste Special-Values smarticon to my
standard toolbar.

I tried using paste as value and it didnt work either excel just
removes all the commas. I did try the following adaptation of Peo's
formula:
=IF(RIGHT(A1)=",",LEFT(A1,LEN(A1)-1),"") this produced a colum that
takes all the entries with a comma at the end and removes the comma
and enters it with all other commas in tact into the colum, I can
paste spoecial values with this and produce a column that has all the
correted entries however all the origannly correct entries are left
blank, if I could only reproduce the correct origanal entries (with
commas in tact into the same row I think I would have this liked.

How are the columns from which you are copying formatted?
If I highlight the column and click format cells, there is nothing
highlighted, but if I go into an individual cell and highlight the cell and
click format cells they are alreadu formatted as numbers