Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Remove Trailing Commas

  1. #1
    Join Date
    13th March 2008
    Posts
    17

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th July 2006
    Posts
    124

    Re: Removing Trailing Commas

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    13th March 2008
    Posts
    17

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,539

    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!

  5. #5
    Join Date
    25th July 2006
    Posts
    124

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    13th March 2008
    Posts
    17

    Re: Removing Trailing Commas

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th July 2006
    Posts
    124

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    13th March 2008
    Posts
    17

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    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 at 14:29.
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  10. #10
    Join Date
    13th March 2008
    Posts
    17

    Re: Remove Trailing Commas

    Thank you shg,

    I can make sense of that formula,

    Great reasoning behind it that.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Remove Trailing Commas From Column
    By dzap79 in forum EXCEL HELP
    Replies: 1
    Last Post: March 14th, 2009, 02:24
  2. Remove Trailing Carriage Returns
    By mattri in forum EXCEL HELP
    Replies: 14
    Last Post: May 21st, 2007, 18:25
  3. Remove Trailing Spaces
    By Blaine Gunther in forum EXCEL HELP
    Replies: 7
    Last Post: January 9th, 2007, 01:08
  4. Remove Trailing Spaces
    By Imbuzi in forum EXCEL HELP
    Replies: 9
    Last Post: October 30th, 2006, 10:02
  5. Cannot Remove Trailing Spaces
    By mbluethunder700 in forum EXCEL HELP
    Replies: 12
    Last Post: September 25th, 2006, 05:23

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno