Extracting delimited substring in Excel

I started with this question, but it doesn't quite address my needs. I have a series of filenames in a worksheet. Some of the filenames have cross-references enclosed in square brackets. Consider Foul language [see Angry Customer]. We need to create a list of JUST the cross references, so what I need to do is build a statement that searches a field for "[" or "]". If neither is present, set the field to "". If both are present, extract the substring starting with "[" and ending with "]". For the time being, I'm not concerned with instances where there is an occurance of only ONE square bracket.

Here's where I am so far. The following statement works great and returns [x-ref value] where it should, but instances where there are no occurences of brackets default to #VALUE!. I'd like them to default to null or a single space. Is this even possible since MID is expecting two numeric parameters instead of null\space? I was thinking that the error would escape out of MID calculations and populate the cell with "" BEFORE we got to the start-position.

=MID(F11,(IFERROR((FIND("[",F11))," ")),(IFERROR(((FIND("]",F11))-((FIND("[",F11))-1))," ")))

Tips?

dwwilson66

Posted 2013-02-13T14:04:56.623

Reputation: 1 519

Great answer. I was overthinking it...If I read this right, you're applying the IFERROR to the MID function rather than the MID function on a set of IFFERROR(FIND.... Am I looking at this correctly? – dwwilson66 – 2013-02-13T14:20:43.577

Yes - the background is that Excel will "forward" any error - so if any of the SEARCH functions return an error, MID will also result in an error. Therefore, we only need to capture any errors the MID produces, as this will include any of the SEARCHs... – Peter Albert – 2013-02-13T14:22:45.233

1That makes sense...now that I know how Excel internals work. :) Thanks – dwwilson66 – 2013-02-13T14:26:52.703

Extracting delimited substring in Excel

Answers