Removing Square brackets or Speech Marks

[Swarmthief]

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

 

[VoG]

Try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"[",""),"]",""),CHAR(34),"")

 

[Swarmthief]

Epic, thanks Peter, that works a treat!