0

I have a field in Excel that shows filtering logic based on certain ids, and I would like to extract for example, the 1st, 2nd, 3rd, 4th occurrences of a specific id.

Let's say we have a column that contains a value such as: (a=123 OR a=4567) AND (b=523 OR b=456 OR b=67950) AND b=4

and I want to have formulas that can return the 'b' ids such as "b=523", "b=456", "b=67950", and "b=4" in separate columns

From what I can tell, I can't quite use the regular nth occurrence formula using a FIND/SUBSTITUTE using "b=*", because I don't think there is a numeric wildcard, the numerical parts of the ids are not a set length, and they can terminate with " ", ")", or the end of the string.

Is there anything I'm overlooking that I could use to extract these values?

user3453416
  • 1
  • A task for VBA really, and you should use [regex](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) for the pattern matching – bn_ln Jul 12 '22 at 04:16
  • If you don't mind adding some helper columns: substitute all of the terminating characters with another character (call this #), and add that character to the end of the string too. Find the position of the first 'b', call this "x". From That position, find the next #. Something like =find("#",A1,x) call this "y". Then use Mid to extract the string you want. Something like MID(A1,x,y-x). Repeat for all the others, using the Previous y to find the Next b. However, personally, I would use Regex as suggested by bn_ln, And, Bookmark that link!... it's very comprehensive. – Stax Jul 12 '22 at 04:28

0 Answers0