+ Reply to Thread
Results 1 to 7 of 7

Can you serach an array and extract a value and input that value into another cell?

  1. #1
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Can you serach an array and extract a value and input that value into another cell?

    Hello

    I'm trying to cut and paste data into a workbook without having to manually type in the data into each cell. When I copy the data it pastes all along column A.

    From column A, I'd like to extract certain data to column H.

    Problem: The data is not always in a specific cell, such as listed here (attached) in cell A29. It could be in A27:A32. Is there function that will allow me to search an array for a specific value (in this case a date), extract a value, and input that value into a certain cell, such as the date in H29?
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,140

    Re: Can you serach an array and extract a value and input that value into another cell?

    hi there. what would be in other cells? you can use the MATCH formula to find a certain keyword. i used ":" in this case:
    =MID(INDEX(A27:A32,MATCH("*:*",A27:A32,0)),FIND(":",INDEX(A27:A32,MATCH("*:*",A27:A32,0)))+1,100)

    or even find the date format:
    =MID(INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)),FIND(":",INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)))+1,100)

    but do note that LEFT, RIGHT, and MID will make the extraction a text. Dates are better handled as numbers. to convert them, simply put double negations in front:
    =--MID(INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)),FIND(":",INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)))+2,100)
    format it as a date.
    i also just realised it should be "+2" instead of "+1" since the date is 2 characters from ":"

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Re: Can you serach an array and extract a value and input that value into another cell?

    Thank you!

    This worked for the date: =--MID(INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)),FIND(":",INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)))+2,100)

    What if I wanted to find only one of several possible text strings in A27:32, such as "Blue," "Red," or "Orange." Only one of those 3 possibilities would exist in that range and I'd like to capture it and insert it into a particular cell. Thinking I'd use the match formula below and instead of "*:*", what would it be?

    =MID(INDEX(A27:A32,MATCH("*:*",A27:A32,0)),FIND(":",INDEX(A27:A32,MATCH("*:*",A27:A32,0)))+1,100)

  4. #4
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Re: Can you serach an array and extract a value and input that value into another cell?

    Can you also take a look at this for me please? Cell A10 varies. It appears in Cell A8:A12. I'm currently using a search vlookup formula to correlate it to its corresponding location, but that only works if the data remains in one cell. I can't figure out how to combine your match formula w/ the vlookup. See attached.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    13,927

    Re: Can you serach an array and extract a value and input that value into another cell?

    I am just guessing that "Cell A10 varies. It appears in Cell A8:A12." means the text in A10 can appear multiple times and that you need to find the values from column K that match each occurrence. If that is the case then the following proposed solution works.
    A helper column, which may be moved and/or hidden for aesthetic purposes, is added and populated using*: =SEARCH(J2,A$10:A$12)
    The following array entered formula* populates H10:H12
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Array entered formulas (and functions) are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Re: Can you serach an array and extract a value and input that value into another cell?

    Thank you JeteMc.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    13,927

    Re: Can you serach an array and extract a value and input that value into another cell?

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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] Simple Extract cell contents per input conditions
    By Sekars in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-29-2017, 02:04 AM
  2. ListBox input; How do you extract it to another cell
    By Doverwood in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2014, 12:18 PM
  3. extract data from an input cell
    By thmehr in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-26-2013, 05:22 AM
  4. Replies: 3
    Last Post: 03-09-2012, 01:21 PM
  5. Replies: 2
    Last Post: 04-01-2010, 03:54 PM
  6. Creating a serach-database for various input
    By HenkE\\\ in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-26-2007, 10:41 AM
  7. [SOLVED] serach using a cell value
    By BrianP in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-15-2005, 11:05 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