Removing Square brackets or Speech Marks

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Removing Square brackets or Speech Marks

  1. #1
    New Member
    Join Date
    Apr 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Removing Square brackets or Speech Marks

     
    Hello everybody,

    I've got a problem that goes as follows;

    I have three types of data;
    [dogs]
    "dogs"
    dogs

    I want to return dogs for all three i.e. remove either square brackets or speech marks.

    Assuming that the data starts in cell A2 the formula I put together goes thus;

    =IF(FIND("[",A2,1),MID(A2,FIND("[",A2)+1,FIND("]",A2)-2),IF(FIND("""",A2,1),MID(A2,FIND("""",A2)+1,FIND("""",A2)-2),A2))

    However instead of following the IF=FALSE path into the next IF statement the formula returns #VALUE! if the data does not contain [.

    What have I done wrong?

    Thanks in advance

    Swarmthief

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Removing Square brackets or Speech Marks

    Try

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"[",""),"]",""),CHAR(34),"")
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    New Member
    Join Date
    Apr 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing Square brackets or Speech Marks

      
    Epic, thanks Peter, that works a treat!


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