+ Reply to Thread
Results 1 to 7 of 7

Returning all that matches criteria across more cells

  1. #1
    Registered User
    Join Date
    01-03-2015
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    40

    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.

    Could you please help out?

    Thank you very much.
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966

    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
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Registered User
    Join Date
    01-03-2015
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    40

    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. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,567

    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.
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    01-03-2015
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    40

    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. #6
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966

    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. #7
    Registered User
    Join Date
    01-03-2015
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    40

    Re: Returning all that matches criteria across more cells

    Thank you very much good sir

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Indenting cells with VBA where value in another cell matches criteria
    By barefaced66 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-19-2015, 10:46 AM
  2. Crosschecking cells and removing matches based on a criteria.
    By ravogt91 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2014, 07:20 AM
  3. [SOLVED] Populating cells with data that matches certain criteria
    By coach.32 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2013, 10:13 PM
  4. Replies: 5
    Last Post: 10-19-2012, 07:42 AM
  5. Find unique matches for multiple criteria within a row of cells
    By Chase in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2012, 03:11 PM
  6. If values in 2 cells matches criteria, add text to a third cell
    By Zarkov in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-31-2011, 03:14 PM
  7. Counting cells that matches any of multiple criteria
    By JeePee in forum Excel General
    Replies: 2
    Last Post: 10-02-2011, 07:34 AM

Bookmarks

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