Remove Trailing Commas

bigtonyicu · Mar 14th 2008

Removing Trailing Commas

I have a list (general ledger) GL codes with trailing commas

Example:
123456789,123456722,123789456,,,,,

When I try to use “replace” and replace the multiple commas with nothing it turns my gl codes into a scientific number (1.23456789123456E+26), but my cell is formatted to text.

The other problem is for numbers with a single trailing comma, it would also remove the commas between the GL codes

Is there a function that removes trailing commas, or commas that are not followed by numbers?

Thanks

Anthony

darkyam · Mar 14th 2008

Re: Removing Trailing Commas

=left(a1,find(",,",a1)-1)

bigtonyicu · Mar 14th 2008

Re: Removing Trailing Commas

That works it there's multiple commas but what if there's only 1 comma at the end that gives me an error

**norie** · Mar 14th 2008

Re: Removing Trailing Commas

Anthony

What result do you actually want?

If it's each GL code in a seperate cell then use Data>Text to columns... with comma as the delimiter.

darkyam · Mar 14th 2008

Re: Removing Trailing Commas

Assuming the end of all cells is ",", =IF(Right(A1,2)=",,",left(a1, find(",,",a1)-1),Left(A1,Len(A1)-1))

bigtonyicu · Mar 14th 2008

Re: Removing Trailing Commas

So essentially there are no formulas that can be used for single commas and multiple trailing commas?

darkyam · Mar 14th 2008

Re: Removing Trailing Commas

Did the formula I give you not work? You could add another IF loop if there are cells that don't end with a comma, but I think what I put in should do the job for you if they all end in at least one comma.

bigtonyicu · Mar 14th 2008

Re: Removing Trailing Commas

That strange I tried it on my data file, and it didn't work... I just copied a sample line to a new doc (so that I could post it online) and now it works... but when I go back to the other doc I get the mistake again, So I’ll just copy all the rows from my old file to a new one and I should be golden,... it's not your formula, it's the file (or my excel acting up).

Thank you SOOOOO much (because I have the same problems with trailing zeros... and those mess up my files, you can't see them with out entering the file, so they keep coming back from validation with errors).

I OWE YOU

shg · Mar 15th 2008

Re: Remove Trailing Commas

Or this: [COLOR="Blue"]= SUBSTITUTE(TRIM(SUBSTITUTE(A1, ",", " "))," ", ",")[/COLOR]

It will remove trailing commas for cells with comma-delimited values ending with zero or more commas.

bigtonyicu · Mar 17th 2008

Re: Remove Trailing Commas

Thank you shg,

I can make sense of that formula,

Great reasoning behind it that.

GaneshRinge · May 30th 2013

Re: Remove Trailing Commas

Hi,

Please suggest, how to remove multiple commas in formula

exp. [TABLE="width: 547"]

[tr]

[TD="class: xl66, width: 547"][TABLE="width: 547"]

[tr]

[TD="class: xl66, width: 547"]0 - 6 Months , 6 - 12 Months , 1 - 2 Years , 2 - 3 Years , , , , , , , , , , , , , , , , , , [/TD]

[/tr]

[/TABLE]
[/TD]

[/tr]

[/TABLE]
[TABLE="width: 547"]

[tr]

[TD="class: xl66, width: 547"] , , , , 3 - 4 Years , 4 - 5 Years , 5 - 6 Years , 6 - 7 Years , 7 - 8 Years , 8 - 9 Years , 9 - 10 Years , 10 - 11 Years , 11 - 12 Years , , , , , , , , ,

This formula : =IF(ISERROR(VLOOKUP(CONCATENATE(AK14,AL14,AM14),'Age data'!$AA$1:$AC$712,3,0))," ",(VLOOKUP(CONCATENATE(AK14,AL14,AM14),'Age data'!$AA$1:$AC$712,3,0)))[/TD]

[/tr]

[/TABLE]

**cytop** · May 30th 2013

Re: Remove Trailing Commas

Please do not post questions in threads started by other users.

Start your own thread, give it an accurate and concise title and explain your issue fully. If you think any other thread can help clarify your problem then you can include a link by copying the URL from the address bar of your browser and pasting into your message.

Remove Trailing Commas

Participate now!

Share