Announcement

Collapse
No announcement yet.

Remove Trailing Commas

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Remove Trailing Commas



    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

  • #2
    Re: Removing Trailing Commas

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

    Comment


    • #3
      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

      Comment


      • #4
        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.
        Boo!

        Comment


        • #5
          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))

          Comment


          • #6
            Re: Removing Trailing Commas

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

            Comment


            • #7
              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.

              Comment


              • #8
                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

                Comment


                • #9
                  Re: Remove Trailing Commas

                  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, 14:29.
                  Entia non sunt multiplicanda sine necessitate.

                  Comment


                  • #10
                    Re: Remove Trailing Commas

                    Thank you shg,

                    I can make sense of that formula,

                    Great reasoning behind it that.

                    Comment


                    • #11
                      Re: Remove Trailing Commas

                      Hi,

                      Please suggest, how to remove multiple commas in formula

                      exp.
                      0 - 6 Months , 6 - 12 Months , 1 - 2 Years , 2 - 3 Years , , , , , , , , , , , , , , , , , ,
                      , , , , 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)))

                      Comment


                      • #12


                        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.
                        Last edited by cytop; May 30th, 2013, 15:43.

                        Comment

                        Working...
                        X