Extracting text from an Excel ws cell based on a list search

I don't have any experience with Mac, but I was able to open your workbook. This formula seems to work:

=IF(LEN(MAX(IFERROR(--RIGHT(MID(A2,1,ROW(INDIRECT("1:"&LEN(A2)))),{1,2,3,4,5}),0)))=5,IF(MID(A2,FIND("(",A2)+1,1)="M",MID(A2,FIND("(",A2)+2,MIN(IFERROR(FIND({"/",",","_",")"},A2)-FIND("(",A2)-2,""))),MID(A2,FIND("(",A2)+1,MIN(IFERROR(FIND({"/",",","_",")"},A2)-FIND("(",A2)-1,"")))),"")

Select allOpen in new window

This is an array formula so after you paste it select ctlr & shift & enter. I did have to make one minor change in your data. In A11 you have "V36100 - Relocate Existing." I had to change the "-" to "_". The original formula looked for any of these four characters: "/  , - )". The Len of those characters are used to determine how many characters are displayed in the Mid statement. The data in A26:A29 was causing an error in that statement. The formula now looks for the underscore. Hope it works for you.

Paul
Sample-Equipment-Hunt091219.xlsx

Paul, thanks for your help!  It looks like your formula is not referring to the List worksheet when deciding what to extract from column A. Is that correct? I'm looking for a way to use a separate list (in List worksheet) to display in column C in the Jobs worksheet the value(s) found from the list.

The formula I have in column B in the Jobs worksheet displays only the number of occurrences in column A that are in the list.

Correct. Mine was giving you the first number listed in column A. So in the "Want" column you want the number that appears in column A only if it is listed on the List sheet? The only data that matched in your example was in A6, which had two of the numbers in the list. What should happen in those cases?

Yes, in the Want column, display the the first number in column A that appears in the list. In the have column, I can see if there is more than one. That will suffice. If you can get all the numbers from column A that appear in the list and separate them by a delimiter, that would be great. Thanks.

Try this code. It runs the previous code anytime the value in column B is more than zero.

=IF(AND(B2>0,LEN(MAX(IFERROR(--RIGHT(MID(A2,1,ROW(INDIRECT("1:"&LEN(A2)))),{1,2,3,4,5}),0)))=5),IF(MID(A2,FIND("(",A2)+1,1)="M",MID(A2,FIND("(",A2)+2,MIN(IFERROR(FIND({"/",",","_",")"},A2)-FIND("(",A2)-2,""))),MID(A2,FIND("(",A2)+1,MIN(IFERROR(FIND({"/",",","_",")"},A2)-FIND("(",A2)-1,"")))),"")

Select allOpen in new window

Sorry, I just get blanks. See attached pic. Most of them should be blank except for C6.

I tried the CNTRL-SHIFT-ENTER and it didn't do anything.
Formula-Result.pdf

It is an array formula and needs the Ctrl+Shift+Enter. From your .pdf, it doesn't look like an array formula. They are enclosed with { }. Try holding down the Ctrl & Shift buttons first, then hit enter. I attached a working sample.
Sample-Equipment-Hunt091319.xlsx

That works! Thanks, I had the formula in the wrong column.

That's a nice interim solution but is I think what you are doing is taking the first number in column A if column B is non-zero. Is that right?

If so, it could very easily be that the first number is not one from the list. Any ideas for checking against the list rather than just taking the first number?

Sorry. I made the formula to match what you had in the want column. So if P36150A/B appeared in the list, you would want P36150A to appear in D15 instead of P36122A?

Paul,

Thanks a ton for sticking with it. That is just what I was looking for.

Ed

My pleasure Ed. It's fun working these problems out!