Results 1 to 3 of 3

Thread: Removing Square brackets or Speech Marks

  1. #1

    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

    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

    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