+ Reply to Thread
Results 1 to 8 of 8

Searching for text strings - need to output the cell they are found in

  1. #1
    Registered User
    Join Date
    07-09-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    5

    Searching for text strings - need to output the cell they are found in

    Hi -

    Hope you're all well. I wanted to search for multiple text strings in excel and need to know which cells they are found in. So, for example, if there are 200 cells with text / numbers and the word "excel 101" appears in cells B2, C2, Z3 and AA3, I'd like excel to output these cells onto the sheet.

    I dont think i can do this without VBA, but would appreciate your thoughts. I've attached the spreadsheet that shows me getting it working to confirm that the text does exist (Tab1), but I cannot get Tab 2 to work...

    It would be great if you could help with this - with or without VBA



    Dante
    Attached Files Attached Files

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

    Re: Searching for text strings - need to output the cell they are found in

    Hi,

    Couldn't find a cell reference to "Philip" anywhere in Tab2 so had to hard-code it in the formula, but something like this array** formula in cell E5 and copied across:

    =IF(COLUMNS($A:A)>COUNTIF($B$9:$B$65,"*"&"Philip"&"*"),"",ADDRESS(SMALL(IF(ISNUMBER(SEARCH("*"&"Philip"&"*",$B$9:$B$65)),ROW($B$9:$B$65)),COLUMNS($A:A)),COLUMN($B$9:$B$65),4))

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

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

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Searching for text strings - need to output the cell they are found in

    Hi,

    Here's a Pivot Table approach. Can you work with that.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    07-09-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Searching for text strings - need to output the cell they are found in

    Richard - that is clever and would work for my simple sample to be fair, but in the data I am looking at there wont be exact matches - the cells will contain more text rather than just the names.

    Sorry - this was my fault, I should have explained this, but I do like the workaround and with the pivot and it has helped me with something related

    If you look at tab1 - cells F3 / F4 would show the corresponding cell where the text is? See Book_1

    Does that make sense?

    Dante
    Attached Files Attached Files

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Searching for text strings - need to output the cell they are found in

    Hi,

    I don't understand your data. In 'Book_1' you indicate that the word 'the' occurs in cells C13, C15 & C16. The last two are blank, and C14 contains the word 'the'.



    Don't forget when doing finds and matches that you can use wild card characters to find strings that are embedded in other strings.

  6. #6
    Registered User
    Join Date
    07-09-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Searching for text strings - need to output the cell they are found in

    Hi -

    Thanks for the response. Basically, what I am trying to do is :

    - search multiple cells for text
    - the text in each cell will be long e.g. contain the sentence cell B2 = "i enjoy running" , B400 = "running is good for you" and cell B1234 = "running is a good challenge"
    - there would be other cells that dont contain the word "running"
    - I would be searching for the word "runnning" and excel would return a list of cells where the word "running" is contained
    - i want to be able to replicate this for multiple words and search upwards of 10,000 cells with text in it for words or phrases that could appear in any order

    I can easily search for exact matches, but i need to be able to search for text strings and output the cells where the text strings appear.

    Does that make sense?

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Searching for text strings - need to output the cell they are found in

    Hi,

    I can't help thinking you need some fuzzy logic here. There was a similar thread earlier this year - see link below. Take a look at post #4 where I gave the link to an Add In Microsoft had developed for just this purpose. You may find that useful for your requirement.

    http://www.excelforum.com/excel-form...n-or-cell.html

  8. #8
    Registered User
    Join Date
    07-09-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Searching for text strings - need to output the cell they are found in

    Hi there - many thanks, I will have a look at the thread and see if it helps answer the question.

    Appreciate the time people have taken to respond

    Dante

+ 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. Help searching an array of text strings for common strings
    By ABComp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-23-2012, 11:19 PM
  2. Replies: 3
    Last Post: 05-25-2012, 05:45 PM
  3. Error in this simple code to output cell content if text is found in certain cells
    By pylauzier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-01-2011, 10:35 AM
  4. Searching for text strings within cells... how?
    By shadestreet in forum Excel General
    Replies: 5
    Last Post: 11-10-2008, 08:50 AM
  5. [SOLVED] searching for multiple text strings
    By eddie in forum Excel General
    Replies: 4
    Last Post: 04-10-2005, 06:06 PM

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