Removing Trailing Commas
I have a list (general ledger) GL codes with trailing commas
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?
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.
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
Or this: = SUBSTITUTE(TRIM(SUBSTITUTE(A1, ",", " "))," ", ",")
It will remove trailing commas for cells with comma-delimited values ending with zero or more commas.
Last edited by shg; March 15th, 2008 at 14:29.
Entia non sunt multiplicanda sine necessitate.
MS MVP - Excel
There are currently 2 users browsing this thread. (0 members and 2 guests)