Results 1 to 3 of 3

Removing Square brackets or Speech Marks

This is a discussion on Removing Square brackets or Speech Marks within the Excel Questions forums, part of the Question Forums category; Hello everybody, I've got a problem that goes as follows; I have three types of data; [dogs] "dogs" dogs I ...

  1. #1
    New Member
    Join Date
    Apr 2010
    Posts
    2

    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
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,480

    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

    Default Re: Removing Square brackets or Speech Marks

    Epic, thanks Peter, that works a treat!


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