Results 1 to 5 of 5

Thread: Search all cells in a column for text and if found provide the cell reference

  1. #1

    Search all cells in a column for text and if found provide the cell reference



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there,

    I have a list of data around 2000 rows long in column A. I'd like to search through the list and when a record contains specific text I'd like the formula to return that row number (ideally the cell reference) so that I can perform additional manipulation of the text in the cell.
    In the list of data there will be only around 4 records that have the string, but I need a formula/series of formulas that won't just stop at the first instance.

    For testing purposes I've tried this {=SMALL(IF(A:A=F1,ROW(A:A)),ROW(1:1))} where F1 holds the exact value and it returns the row correctly. I then have 3 more rows with Row 2:2 etc on the end.

    However I really need something like this {=SMALL(IF(A:A="*string*",ROW(A:A)),ROW(1:1))} where I can enter the string/text into the formula with a wild card on each end, but it just won't work!

    Any ideas?


    Regards,

    Phil

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    how are you at VBA?
    You could do this fairly easily with code.
    Try looking up "instr"

  3. #3
    Haven't used VBA in years. There are around 100 different errors I need to scan the data for, I feel VBA would be more work... once I can get this formula to work I can copy paste it everywhere. But thanks though

    Quote Originally Posted by Simi View Post
    how are you at VBA?
    You could do this fairly easily with code.
    Try looking up "instr"

  4. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    could you provide a sample workbook with what you are trying to accomplish? that may help get more responses to help.

  5. #5

    Won the game.

    Quote Originally Posted by Simi View Post
    could you provide a sample workbook with what you are trying to accomplish? that may help get more responses to help.
    Just figured it out! Very proud of myself.

    {=SMALL(IF(ISNUMBER(FIND(O39,Data!A:A)),ROW(Data!A:A)),ROW(Data!1:1))}
    Where O39 holds the string I am searching for.
    OR for a string in the formula
    {=SMALL(IF(ISNUMBER(FIND("text goes here",Data!$A:$A)),ROW(Data!$A:$A)),ROW(Data!1:1))}

    The last ROW argument is increased by one for the next instance you want to find, for example:
    {=SMALL(IF(ISNUMBER(FIND(O39,Data!A:A)),ROW(Data!A:A)),ROW(Data!2:2))} etc

    Hopefully this helps someone else in the future! Thanks for everyone's feedback though!

Posting Permissions

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