Delete first "," (comma) from left in a alphanumeric string..

  • Re: Delete first "," (comma) from left in a alphanumeric string..


    Thanks apo :)!


    Basically my string is as below:


    ABC:5,622.871ms,Call:0ms,Stop:0ms,Halt:2,399.921ms


    When I want to delimit on , (comma) problem is that the numeric string contains commas which I want removed first.


    So your above formula worked great and my result now looks as below:



    ABC:5622.871ms,Call:0ms,Stop:0ms,Halt:2,399.921ms


    So comma in 5,622.871 is takem care of. In the same way can i remove first comma from right (for 2,399.921)?


    Thanks.

  • Re: Delete first "," (comma) from left in a alphanumeric string..


    I was just looking at your other thread and noticed you would need to remove those commas if you want to split the cell value by commas..


    Try this to remove the last comma..


    =REPLACE(A1,FIND("@",SUBSTITUTE(A1,",","@",(LEN(A1)-LEN(SUBSTITUTE(A1,",","")))/LEN(","))),1,"")


    I got the idea (and most of the formula) from here..


    http://stackoverflow.com/quest…character-match-in-string

  • Re: Delete first "," (comma) from left in a alphanumeric string..


    This formulas also work:


    To delete the first comma:


    =SUBSTITUTE(A1,",","",1)


    To delete the last comma:


    =SUBSTITUTE(A1,",","",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))