+ Reply to Thread
Results 1 to 4 of 4

Finding text string within Range and returning Row/Column number

  1. #1
    Registered User
    Join Date
    09-09-2012
    Location
    Bradford, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Finding text string within Range and returning Row/Column number

    Hi, I have problem with finding text from a range. I tried the following formula, but my text has trailing spaces afterwards so it's not finding it. I tried inserting an asterix as a wildcard after the text to find but again it came back with an error.

    B1: =ADDRESS(MAX(INDEX((B2:D100="PET*")*ROW(B2:D100),0)),MAX(INDEX((B2:D100="PET*")*COLUMN(B2:D100),0)),4)


    What I want to achieve:

    I want to be able to import a report into my spreadsheet which may, or may not have the required header names in it. For example, I may have a report WITH header names PET, NAME, AGE ("_" characters denote trailing spaces):

    A B C D
    1
    2 PET___ NAME_ AGE__
    3 Cat Snowy 7
    4 Dog Tommy 3
    5 Cat Felix 10


    Or WITHOUT header names:

    A B C D
    1
    2
    3 Cat Snowy 7
    4 Dog Tommy 3
    5 Cat Felix 10

    The report could be pasted in randomly on the sheet, i.e. starting on cell D8.

    Then I want to..
    1. Find the text "PET" from a cell within the range B2:D100
    2. If it finds "PET" then return the row number in cell B1 (use this row number as the starting point for columns C and D, i.e if row number of "PET" is 5 then retrieve text from cell C5 "NAME" to C1 and cell D5 "AGE" to D1)
    3. If it does not find "PET" then we create a header of the same name
    4. With my found, or newly created header names, I can then determine what from the report is a header and what isn't, and treat them accordingly when carrying out lookups on the different elements of the report.

    I hope that makes sense, and that someone can help.

    Many Thanks, Ian

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Finding text string within Range and returning Row/Column number

    Hi Ian,

    You are close. Try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    entered as ctrl + shift + Enter not just enter
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    09-09-2012
    Location
    Bradford, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Finding text string within Range and returning Row/Column number

    Thanks very much abousetta. Brilliant, works like a treat!

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Finding text string within Range and returning Row/Column number

    No need to thank me... it was all you. I just added the trim function.

    Good luck and thanks for the feedback.

    abousetta

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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