+ Reply to Thread
Results 1 to 12 of 12

Formula to identify part of a text in cell from range of cells & insert adjacent cell text

  1. #1
    Registered User
    Join Date
    11-16-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Formula to identify part of a text in cell from range of cells & insert adjacent cell text

    Hi I'm trying to create a formula that would identify part of some text in a cell from a range of cells (column) in another spreadsheet and once it identifies the text insert the text from the adjacent cell.

    I thought the formula =LOOKUP(A3,'Sheet1'!$B3:$B100,$C3:$C100)

    Where A3 contains the text and the cells within the range B3:B100 will contain part of the text in A3 and the text in the cell adjacent to the cell containing the text of interest (range C3:C100) will then appear in the cell I put the formula in.

    Hope this makes sense.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    Hi and welcome to the forum!

    Are you able to upload a small sample workbook with a few examples and your desired result in each case?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    11-16-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    Thanks for the welcome!
    I dont seem to be able to upload a sample workbook, but this is what I'm hoping to achieve

    Sheet1
    Row A B C D
    1 cat food
    2 dog water
    3 goat grass
    4 elephant peanut

    Sheet 2
    A B C D
    1 cat 722comp =LOOKUP(A1,Sheet1!A1:A4,B1:B4)
    2 dog 643 comp
    3 sheep 487 comp

    Ok so what i want to do is create a forumla that will use the information in the cell sheet 2 A1 (cat 722comp) to identify part of that text in the range; sheet 1 A1:A4 (cat) and then paste in sheet 2 B1 the text in the adjacent cell Sheet 1 B1 (food). The idea then would be to repeat the formula for the subsequent cells in sheet 2 column A.

    Hope that makes sense.
    Last edited by Novicebutnotforlong; 11-16-2013 at 06:35 AM. Reason: sent it too early

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    Sorry - not sure I understand. The two tables appear to be identical (?). Also, you've only put one example in there, so it's very difficult to determine exactly what you want.

    What difficulties did you have in trying to upload the attachment?

    Regards

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

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    welcome to the forum. what if the data in sheet2 is "doggie"? does it return "water" too or must a single word be "dog"? for the former, try:
    =IFERROR(LOOKUP(2^15,SEARCH(Sheet1!$A$1:$A$4,A1),Sheet1!$B$1:$B$4),"")

    for the latter:
    =IFERROR(LOOKUP(2^15,SEARCH(" "&Sheet1!$A$1:$A$4&" "," "&A1&" "),Sheet1!$B$1:$B$4),"")
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-16-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    Example Formula.xlsx

    I worked out how to attach the spreadsheet. I would like sheet 2 to display the description by searching the accession numbers from column A in sheet 1.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-16-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    Example Formula with suggested formula.xlsx

    Hi again,

    I've uploaded the spreadsheet with the suggested formula. Any hints as to why it isn't working will be greatly appreciated.
    Last edited by Novicebutnotforlong; 11-16-2013 at 06:43 PM.

  8. #8
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    My thoughts would be to approach it along these lines ...

    Reference your file attached in #7 and your explanation in #6:
    ... I would like sheet 2 to display the description by searching the accession numbers from column A in sheet 1 (via matching with col D in Sheet2)

    In Sheet2,
    In N2: =TRIM(MID(D2,SEARCH("_",D2)+1,99))
    Copy down. This strips off the substring to conform with what's in Sheet1 col A

    Then in O2: =INDEX(Sheet1!B:B,MATCH(N2,Sheet1!A:A,0))
    Copy down
    ---------------------
    How does it go for you? Wave it, hit the little star at the bottom left of my responses

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

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    in your scenario, you should be using my 1st formula & not the 2nd one right? and you noticed i used dollar signs to lock my formula? that is to fix the range
    =IFERROR(LOOKUP(2^15,SEARCH(Sheet1!$A$3:$A$11,'Sheet 2'!D2),Sheet1!$B$3:$B$11),"")

    or if you want to put in "No hit", then:
    =IFERROR(LOOKUP(2^15,SEARCH(Sheet1!$A$3:$A$11,'Sheet 2'!D2),Sheet1!$B$3:$B$11),"No hit")

  10. #10
    Registered User
    Join Date
    11-16-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    Thanks!
    That worked brilliantly. I was just thinking is there a way to add to the formula to choose different sheets to search for data depending on what is written in the cell? For example if the cell in sheet 2 column D reads cow, then I would like the formula to search in sheet 1. However, if the cell in column D reads Cat I would like the formula to search in sheet 3.

  11. #11
    Registered User
    Join Date
    11-16-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    Thanks for the suggestion the formula in O2 didn't seem to work & I was hoping to use one formula.

    Cheers!

  12. #12
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Formula to identify part of a text in cell from range of cells & insert adjacent cell

    Reply to Op's post #11
    FWIW, it worked fine in my testing here. And you could collapse the formula in col N into 1 col, eg:
    in O2: =INDEX(Sheet1!B:B,MATCH(TRIM(MID(D2,SEARCH("_",D2)+1,99)),Sheet1!A:A,0))

+ 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] Splitting text from one cell into separate text fragments, Located in adjacent cells
    By onsid in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-15-2013, 08:32 PM
  2. Replies: 7
    Last Post: 01-24-2013, 06:55 PM
  3. [SOLVED] Find empty cells in a range and insert formula in adjacent cell
    By macronovice1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-25-2012, 03:06 PM
  4. Replies: 4
    Last Post: 04-11-2011, 08:41 PM
  5. Insert cell text as part of formula?
    By elazarus in forum Excel General
    Replies: 3
    Last Post: 06-03-2009, 11:24 AM

Tags for this Thread

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