Stripping leading and trailing commas
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Stripping leading and trailing commas

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    C2 contains text that may or may not begin, end, or end&begin with a comma. The text may also have commas in the middle. I want to only strip the commas if they appear on either end.
    ,foo,bar, -> foo,bar
    ,foo,bar -> foo,bar
    foo,bar, -> foo,bar
    ,, ->

    I've been able to accomplish this task with multiple cells, but I figure somebody else could greatly improve upon my feeble attempt.

    In B2 I use =IF(RIGHT(C2,1)=",",REPLACE(C2,LEN(C2),1,""),C2) to strip off trailing commas and
    In A2 I use =IF(LEFT(B2,1)=",",REPLACE(B2,1,1,""),B2) to strip off leading commas.

    In general I'm having trouble figuring out how to have multiple IFs work against a text string.

    TIA.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Try this way:

    =MID(C2,IF(FIND(",",C2)=1,2,1),IF(RIGHT(C2)=",",LEN(C2)-2,LEN(C2)))

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-13 00:51, Skebo wrote:
    C2 contains text that may or may not begin, end, or end&begin with a comma. The text may also have commas in the middle. I want to only strip the commas if they appear on either end.
    ,foo,bar, -> foo,bar
    ,foo,bar -> foo,bar
    foo,bar, -> foo,bar
    ,, ->

    I've been able to accomplish this task with multiple cells, but I figure somebody else could greatly improve upon my feeble attempt.

    In B2 I use =IF(RIGHT(C2,1)=",",REPLACE(C2,LEN(C2),1,""),C2) to strip off trailing commas and
    In A2 I use =IF(LEFT(B2,1)=",",REPLACE(B2,1,1,""),B2) to strip off leading commas.

    In general I'm having trouble figuring out how to have multiple IFs work against a text string.

    TIA.
    Skebo,

    How about:

    =IF(AND(LEFT(A1)=",",RIGHT(A1)=","),MID(A1,2,LEN(A1)-2),IF(LEFT(A1)=",",MID(A1,2,LEN(A1)),IF(RIGHT(A1)=",",LEFT(A1,LEN(A1)-1),A1)))

    where A1 houses the target string.

    Aladin

  4. #4
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    799
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF(LEFT(C2,1)=",",IF(RIGHT(RIGHT(C2,LEN(C2)-1),1)=",",LEFT(RIGHT(C2,LEN(C2)-1),LEN(RIGHT(C2,LEN(C2)-1))-1),C2),IF(RIGHT(C2,1)=",",LEFT(C2,LEN(C2)-1),C2))

    regards
    suat

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",")

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    Skebo,

    Use the shorter formula Mark suggested.

    I believe those IFs are not entirely wasted as you were interested in its use explicitly. A small compensation I'd say

    Aladin


    On 2002-03-13 01:14, Aladin Akyurek wrote:
    On 2002-03-13 00:51, Skebo wrote:
    C2 contains text that may or may not begin, end, or end&begin with a comma. The text may also have commas in the middle. I want to only strip the commas if they appear on either end.
    ,foo,bar, -> foo,bar
    ,foo,bar -> foo,bar
    foo,bar, -> foo,bar
    ,, ->

    I've been able to accomplish this task with multiple cells, but I figure somebody else could greatly improve upon my feeble attempt.

    In B2 I use =IF(RIGHT(C2,1)=",",REPLACE(C2,LEN(C2),1,""),C2) to strip off trailing commas and
    In A2 I use =IF(LEFT(B2,1)=",",REPLACE(B2,1,1,""),B2) to strip off leading commas.

    In general I'm having trouble figuring out how to have multiple IFs work against a text string.

    TIA.
    Skebo,

    How about:

    =IF(AND(LEFT(A1)=",",RIGHT(A1)=","),MID(A1,2,LEN(A1)-2),IF(LEFT(A1)=",",MID(A1,2,LEN(A1)),IF(RIGHT(A1)=",",LEFT(A1,LEN(A1)-1),A1)))

    where A1 houses the target string.

    Aladin

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I forgot to mention a case that obviously makes a big difference Sometimes there will be cases were the data has ,, or ,,, or ,,,, etc. These may appear in the middle or on the ends of the string. If they are in the middle multiple commas should change to one comma. No commas should ever appear on the endpoints.

    foo,,bar -> foo,bar
    foo,,bar, -> foo,bar
    foo,bar,, -> foo,bar
    ,,,foo,,bar,, -> foo,bar

    My deepest apologies for leaving this important detail out.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-13 11:07, Skebo wrote:
    Sorry, I forgot to mention a case that obviously makes a big difference Sometimes there will be cases were the data has ,, or ,,, or ,,,, etc. These may appear in the middle or on the ends of the string. If they are in the middle multiple commas should change to one comma. No commas should ever appear on the endpoints.

    foo,,bar -> foo,bar
    foo,,bar, -> foo,bar
    foo,bar,, -> foo,bar
    ,,,foo,,bar,, -> foo,bar

    My deepest apologies for leaving this important detail out.
    =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",")

    ...will work for these cases as well.

  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark,

    I must say that is very slick.

    Thanks a bunch!


  10. #10
    New Member
    Join Date
    Nov 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stripping leading and trailing commas

      
    Quote Originally Posted by Mark W. View Post
    =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",")

    ...will work for these cases as well.

    I have a minor problem with this:

    If there's a space in one of the words then it replaces that space with a comma. For example:

    ANAHEIM, NY RANGERS, NY ISLANDERS, OTTAWA, , , , , , , , ,
    -> ANAHEIM,NY,RANGERS,NY,ISLANDERS,OTTAWA

    Desired output:
    ANAHEIM,NY RANGERS, NY ISLANDERS,OTTAWA

    So basically all I need is to strip the commas from the end. I'm sorry if this is a stupid question, but I just happened to find this thread and tried out Mark's solution... Any help?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com