I have a datafile used to make a website. The datafile is produced in excel
and then saved as a comma delimed file. One of the colums headers is
categoryids this is the category id for each product in the file, and each
product resides in several different categories. For example the categopryid
cell for product X might be:
123,456,789,1011,1012,1416 when I type this number into a cell in excel
excel converts this entry to the following: 123,456,789,101,110,000,000 as
you can see only the first three category references are correct the rest are
incorrect. If I place a comma at the end like this
123,456,789,1011,1012,1416, then excel leaves it alone until I save the file
and reopen it again at which point excel turns it back
to:123,456,789,101,110,000,000 . Thinking that the comma at the end would
solve my problem I added to all entries that excel had changed and uploaded
the saved csv file to the server software and it was rejected because of the
last comma. I was told I need to remove it before it can be accepted. I was
told that I must now open the csv file in notepad and remove all the trailing
commas then upload the file. My question is 2 fold. 1. Is there any way to
stop excel from converting this 123,456,789,1011,1012,1416 into
123,456,789,101,110,000,000 ? 2. I now have a category column for my 150000
products where some of the entries have a trailing "," and some do not. How
do I get rid of the trailing comma without affecting the other commas in the
sequence. The category reference sequence may have 3 or 4 or 5 or 6
different 3 or 4 number category references for the references where I
entered 123,456,789 excel left these alone and didint convert them so I didnt
need to add a comma after these but about 4000 entries remain with the comma
at the end. Thank you for your sharing your expertise.

If you precede the entry with an apostrophe ' or format as text it won't
convert the entries

you can use a help column to remove a trailing comma

=IF(RIGHT(A1)=",",LEFT(A1,LEN(A1)-1),A1)

and copy down the paste special as values

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Jeff" wrote in message
...
I have a datafile used to make a website. The datafile is produced in excel
and then saved as a comma delimed file. One of the colums headers is
categoryids this is the category id for each product in the file, and
each
product resides in several different categories. For example the
categopryid
cell for product X might be:
123,456,789,1011,1012,1416 when I type this number into a cell in excel
excel converts this entry to the following: 123,456,789,101,110,000,000
as
you can see only the first three category references are correct the rest
are
incorrect. If I place a comma at the end like this
123,456,789,1011,1012,1416, then excel leaves it alone until I save the
file
and reopen it again at which point excel turns it back
to:123,456,789,101,110,000,000 . Thinking that the comma at the end would
solve my problem I added to all entries that excel had changed and
uploaded
the saved csv file to the server software and it was rejected because of
the
last comma. I was told I need to remove it before it can be accepted. I
was
told that I must now open the csv file in notepad and remove all the
trailing
commas then upload the file. My question is 2 fold. 1. Is there any way to
stop excel from converting this 123,456,789,1011,1012,1416 into
123,456,789,101,110,000,000 ? 2. I now have a category column for my
150000
products where some of the entries have a trailing "," and some do not.
How
do I get rid of the trailing comma without affecting the other commas in
the
sequence. The category reference sequence may have 3 or 4 or 5 or 6
different 3 or 4 number category references for the references where I
entered 123,456,789 excel left these alone and didint convert them so I
didnt
need to add a comma after these but about 4000 entries remain with the
comma
at the end. Thank you for your sharing your expertise.

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?




"Peo Sjoblom" wrote:

If you precede the entry with an apostrophe ' or format as text it won't
convert the entries

you can use a help column to remove a trailing comma

=IF(RIGHT(A1)=",",LEFT(A1,LEN(A1)-1),A1)

and copy down the paste special as values

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Jeff" wrote in message
...
I have a datafile used to make a website. The datafile is produced in excel
and then saved as a comma delimed file. One of the colums headers is
categoryids this is the category id for each product in the file, and
each
product resides in several different categories. For example the
categopryid
cell for product X might be:
123,456,789,1011,1012,1416 when I type this number into a cell in excel
excel converts this entry to the following: 123,456,789,101,110,000,000
as
you can see only the first three category references are correct the rest
are
incorrect. If I place a comma at the end like this
123,456,789,1011,1012,1416, then excel leaves it alone until I save the
file
and reopen it again at which point excel turns it back
to:123,456,789,101,110,000,000 . Thinking that the comma at the end would
solve my problem I added to all entries that excel had changed and
uploaded
the saved csv file to the server software and it was rejected because of
the
last comma. I was told I need to remove it before it can be accepted. I
was
told that I must now open the csv file in notepad and remove all the
trailing
commas then upload the file. My question is 2 fold. 1. Is there any way to
stop excel from converting this 123,456,789,1011,1012,1416 into
123,456,789,101,110,000,000 ? 2. I now have a category column for my
150000
products where some of the entries have a trailing "," and some do not.
How
do I get rid of the trailing comma without affecting the other commas in
the
sequence. The category reference sequence may have 3 or 4 or 5 or 6
different 3 or 4 number category references for the references where I
entered 123,456,789 excel left these alone and didint convert them so I
didnt
need to add a comma after these but about 4000 entries remain with the
comma
at the end. Thank you for your sharing your expertise.

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

"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)

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.