Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    searching a range for a specific string (excel 2000)

    I have a number of strings in a column in a worksheet, and a column of values that I want to know whether they occur anywhere in the (many) rows of stringsviz:-

    A50A B54M C53B C88A F47A H89R X19E A30R 0
    A50A B54G C53C D05B X19K X22Z A30S 0
    B54G B77B C58F C88A G80F A30T 0
    X19E A30W 0
    C53B X19E A40A 0
    B54G C53C X87C A40B 0
    X19E H89N C53B A40C 0
    G80R A40D 0
    B54G A50A is the string to my left present in any of the strings to the left?
    C53C D05B X19K A50B 0
    D05B X19K A60A 0
    A50A C53C X19K A60B 0
    A50A Z02A A60D 0

    I understand well how to use search to find my target string in one destination cell [=search( searchcellref,targetcellref,1)] and sort of assumed that I could set it looking in a range of cells by using an array. I just cant seem to get the syntax right. Can anyone either help or tell me I cant do it?

    If I have to resort to vba (ouch) it would be quite nice to count the number of time it did occur...

    Thanks,

    Mike C

  2. #2
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: searching a range for a specific string (excel 2000)

    Sorry the actual posting destroyed my nicely laid out example

    here is a tiny spreadsheet example

    Mike C

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: searching a range for a specific string (excel 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 02-Mar-04 06:11. Added Postscript with additional info)</P>This array (confirm with ctrl-shift-enter) will count the number of occurences. Copy it down the column

    <pre>=SUM(IF(ISERROR(SEARCH(E2,burst)),0,1))</pre>



    Steve
    PS. Your formula was "almost there", you did not have the SUM. Without the SUM, each of the formulas is just an Array of 0s and 1s (a 1-column, 12 row array). SInce you only place the array in 1 cell, it only will display the first value. This is 0 in every case because none of the list are in the first item (in B2). Using the SUM, will add all the 0s and 1s (essentially summing the 1s), which gives the number of rows which have a "match".

  4. #4
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: searching a range for a specific string (excel 2000)

    Steve,

    It always seems so clear and logical when the answer is there. I even got my preferred answer as part of the process!

    Thank you both for the solution and for taking the trouble to explain as you did

    Cheers

    Mike C

Posting Permissions

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