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

Edward Pound
Edward Pound used Ask the Experts™
on
See attached spreadsheet. It is MAC ExcelSample-Equipment-Hunt.xlsx

In the Jobs ws in Column A are a list of jobs. In the List ws is a named range called EQUIP.

In Job ws, column B, is a formula that checks each cell against EQUIP and returns the number of occurrences from the list that are in the cell.

Job ws, column C shows the results I want; Display the actual text (could be more than one) that is in the cell and matches EQUIP.

Solution can be a MAC-compatible Excel formula or a VB macro.

Thanks for your help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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,"")))),"")

Open 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

Author

Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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,"")))),"")

Open in new window

Author

Commented:
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

Author

Commented:
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?
OK, see if this is what you're looking for. This is also an array formula. I attached the amended workbook with both formulas. I also extended the data range of "Equip" from A27 to A29 for testing purpose.

=IFERROR(INDEX(EQUIP,MATCH(TRUE,ISNUMBER(SEARCH(EQUIP,A2)),0)),"")

Open in new window

Sample-Equipment-Hunt091319.xlsx

Author

Commented:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial