remove one character from a cell if a condition is met

[Guest]

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.

 

[Peo Sjoblom]

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

 

[Guest]

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?

 

[Paul Lautman]

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

 

[Guest]

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)

 

[Paul Lautman]

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.

 

[Guest]

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.

 

[Paul Lautman]

=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?

 

[Guest]

How are the columns from which you are copying formatted?

The cells have no formatting that I am copying. If I highlight the colum
and select format nothing is selected

 

[Guest]

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

 

[Paul Lautman]

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

And the data in one of those cells that is formatted as number has 4 digits
between the commas yes???
This is what is confusing me. If Excel thinks that it is a number, I would
not expect it to store any commas whatsoever. The commas are purely
formatting. I would not expect a cell formatted as numbers to have 4 digits
between a pair of commas, only 3.

Is there somewhere on the web that you could post a copy of the workbook so
that I can see exactly what it looks like?

 

[Guest]

And the data in one of those cells that is formatted as number has 4 digits
between the commas yes???
This is what is confusing me. If Excel thinks that it is a number, I would
not expect it to store any commas whatsoever. The commas are purely
formatting. I would not expect a cell formatted as numbers to have 4 digits
between a pair of commas, only 3.

Is there somewhere on the web that you could post a copy of the workbook so
that I can see exactly what it looks like?

 

[Guest]

Ok, I guess I figured it out you can view it at the following URL:
www.basketball-goals.com/Prod-Cat.csv

 

[Guest]

You can also view it at this URL:
http://www.basketball-goals.com/Prod-Cat2.htm

 

[Paul Lautman]

Right then Jeff,

I took your Prod-Cat.csv file and used Data->Import External Data->Import
Data... to bring it into Excel via the Text Import Wizard.

In Step 1 of the wizard I specified that it was a Delimted file.
In Step 2 I specified that the Delimiter was a Comma and that the Text
qualifier was a ".
In Step 3 I Selected both columns (currently saying General) using
Shift-Click and selected a column data format of Text.
Then I pressed Finish and the data came in to the sheet.

I then used Peo's formula to remove the trailing commas (obviously changing
the cell references since your data was in column B starting from B2) and
used Copy + Paste Special->Values to put the processed data into B2.

I then deleted the column with the formulas.

I then saved the sheet as a web page and re-exported the data as a new CSV
[see note 1 below] file without the trailing commas. All looked fine.

I then used the Text Import Wizard once again to bring the (now clean) data
back into a new Excel sheet and it all worked fine. The 4 character
categories were preserved in cells formatted as text as expected.

Is there anything that you need to do that does not fit into the above
process?

Regards
Paul

[Note 1] I often automate the import process using a macro. However I find
it is best when automating it, to rename the file as .txt since Excel has a
nasty habit (see previous comment on Lotus 123's superiority in data
handling) of making assumptions with files ending in .csv.

 

[Guest]

Right then Jeff,

I took your Prod-Cat.csv file and used Data->Import External Data->Import
Data... to bring it into Excel via the Text Import Wizard.

In Step 1 of the wizard I specified that it was a Delimted file.
In Step 2 I specified that the Delimiter was a Comma and that the Text
qualifier was a ".
In Step 3 I Selected both columns (currently saying General) using
Shift-Click and selected a column data format of Text.
Then I pressed Finish and the data came in to the sheet.

I then used Peo's formula to remove the trailing commas (obviously changing
the cell references since your data was in column B starting from B2) and
used Copy + Paste Special->Values to put the processed data into B2.

I then deleted the column with the formulas.

I then saved the sheet as a web page and re-exported the data as a new CSV
[see note 1 below] file without the trailing commas. All looked fine.

I then used the Text Import Wizard once again to bring the (now clean) data
back into a new Excel sheet and it all worked fine. The 4 character
categories were preserved in cells formatted as text as expected.

Is there anything that you need to do that does not fit into the above
process?

Regards
Paul

[Note 1] I often automate the import process using a macro. However I find
it is best when automating it, to rename the file as .txt since Excel has a
nasty habit (see previous comment on Lotus 123's superiority in data
handling) of making assumptions with files ending in .csv.

I followed your instrcutions and everything worked like a charm, I saved a
file as a text file and a .csv file, just in case I forget and open the .csv
file with excel I will still have a backup in notepad that I can replace it
with. You would think that with all the people that design web sites in this
fashion that Microsoft would come up with some kind of fix to prevent excel
from making assumptions with .csv files.
In any event I am extremely grateful for youre sharing of your expertise, I
have never used a discussion board of any kind, ever, and have suffered in
silence using excel as a glorified adding machine, their help files are not
the most user friendly, but I was able to follow what you described and
everything worked fine. Thank you again!

Best Regards,

Jeff