"Paul Lautman" wrote:

Jeff wrote:
Thank you for your responce Peo, I already tried formatting the cells
as text and excel still removes all the commas, I only need to remove
the last trailing comma. I am not 100% certain but if I preceed the
entry with an apostrophy and upload it the server software wont
acceopt it. Each category reference must be follwed by a comma
except for the last reference when I do this however excel converts
it to a numeral, if I save the cells as text , and then individually
remove the trailing comma save and close the program and reopen it
excel converts it to numeral again. If I use your formula
=IF(RIGHT(A1)=",",LEFT(A1,LEN(A1)-1),A1) it removes all the commas.
Do you have any other advice?

There is no way that the formula posted by Peo wil remove anything apart
from the final character from a string.

What may be happening is that Excel may be interpreting the answer in a way
that you do not want.

Where Lotus 123 wins hands down over Excel, is that it does not try to
impose what it thinks you want over what you are telling it you want.

Back to your problem. If a cell is formatted as text and you then type in
the string 123,456,789,1011,1012,1416 is will remain unchanged. If instead
of typing it in you say, paste it in, it MAY decide to alter the format to
number. To avoid this use Edit-Paste Special... and select Text. Then it
will stay as you wish.

If you are getting thye text into the cell in a different way, please let us
know and we will let you know what you may do in order to preserve your
formatting.

HTH

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)