Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

You're pretty close to the solution, this should do it:

=IFERROR(MID(A1,SEARCH("[",A1)+1,SEARCH("]",A1)-SEARCH("[",A1)-1),"")
share|improve this answer
    
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 Feb 13 '13 at 14:20
    
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 Feb 13 '13 at 14:22
1  
That makes sense...now that I know how Excel internals work. :) Thanks –  dwwilson66 Feb 13 '13 at 14:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.