Returning all that matches criteria across more cells

1. Returning all that matches criteria across more cells

Hi,

I have A1:A118 full of data, I only need to extract the following: MID(A19;FIND("1057";A19);10) which is fine to get 1 outcome per line, however the issue is there are 3 matching items in 1 cell sometimes, I assume it can be done with INDEX function. Basically I need all items that start with 1057 to be returned.

Thank you very much.

2. Re: Returning all that matches criteria across more cells

Try

=IFERROR(MID(A10,FIND("1057",A10,FIND("1057",A10)+1),10),"NA") in C10
=IFERROR(MID(A10,FIND("1057",A10,FIND("1057",A10,FIND("1057",A10)+1)+1),10),"NA") in D10

3. Re: Returning all that matches criteria across more cells

This works for 3. I'm sorry I have just noticed 2 lines have 6 items matching the criteria. I am afraid this formula would get too complicated for this then. Would there be another solution? Or, could you please write the formula for 4th 5th and 6th item?

I greatly appreciate it, thanks again.

4. Re: Returning all that matches criteria across more cells

I made 3 defined names, Term1, Term2, and Term3. Each one searches for 1057 after the 10 digit number previously found is removed.

Term1 =MID('Sheet1 (2)'!A1,FIND("1057",'Sheet1 (2)'!A1),10)
Term2 =MID(SUBSTITUTE('Sheet1 (2)'!A1, Term1,""), FIND("1057", SUBSTITUTE('Sheet1 (2)'!A1, Term1,"")),10)
Term3 =MID(SUBSTITUTE(SUBSTITUTE(A1, Term1, ""), Term2, ""), FIND("1057", 'Sheet1 (2)'!A1, SUBSTITUTE(SUBSTITUTE(A1, Term1, ""), Term2, "")), 10)

Then in B1 copied down
=IFERROR(Term1,"NA")&" " & IFERROR(Term2,"")& IFERROR(Term3,"")

The way I have the formulas set, only unique numbers will be moved to B (i.e. the same number won't be moved twice in 1 cell)

If the same number is in a cell more than once, do you want it to appear more than once (or won't that ever happen?)?
That would take a minor correction.

5. Re: Returning all that matches criteria across more cells

This is great, I would say if same number appears more than once in a cell, I would say it should appear more than once too (I am not sure if that would happen, but just in case, it could be a mistake in the original file so it is good to notice).

Would it be possible to expand this formula for term 4 5 and 6? I have overlooked that there are up to 6 values that match the criteria in a cell. Sorry for that and thanks a lot.

6. Re: Returning all that matches criteria across more cells

=iferror(mid(a10,find("1057",a10,find("1057",a10,find("1057",a10,find("1057",a10)+1)+1)+1),10),"na")

=iferror(mid(a10,find("1057",a10,find("1057",a10,find("1057",a10,find("1057",a10,find("1057",a10)+1)+1)+1)+1),10),"na")

=iferror(mid(a10,find("1057",a10,find("1057",a10,find("1057",a10,find("1057",a10,find("1057",a10,find("1057",a10)+1)+1)+1)+1)+1),10),"na")

7. Re: Returning all that matches criteria across more cells

Thank you very much good sir

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1