+ Reply to Thread
Results 1 to 30 of 30

Search A Range For Specific Text String

  1. #1
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Search A Range For Specific Text String

    I'm stuck trying to create a formula searching a range on a row to find specific text. The problem I have is the text string could be found in other cells that I need to ignore.

    For example (attached) I want to search for the word "Communication" in the range A:H and return the value in the column to the right. But not where the word "Info" appears. So in my example I want to find the string "Communication" only, but "Information and Communication" also contains the same word, but I want it to ignore cells that have the string "Information" in it.

    Can this be done?
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Search A Range For Specific Text String

    It's certainly possible, but I'm not sure what you want as the output? What do you want to see in column I?? For example,

    =COUNTIF(A2:H2,"Communication") will return a count of the number of cells that contain the word communication on its own, but nothing if the cell contains "information and communication"
    Last edited by Glenn Kennedy; 11-06-2014 at 08:58 AM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    In row 2 of the example, cell C2 meets the criteria so I'm looking for the result in col I to be D2. Hope that makes sense.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Search A Range For Specific Text String

    mmm. Leave it with me.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Search A Range For Specific Text String

    Hi. If I've understood you correctly, this is what you need.
    Attached Files Attached Files

  6. #6
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Search A Range For Specific Text String

    hi,

    May be this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

  7. #7
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    Quote Originally Posted by Glenn Kennedy View Post
    Hi. If I've understood you correctly, this is what you need.
    Yes, and no. It provides the correct result but, I had tried to simplify my example but this may have caused me to give you too little info.

    The actual data I will be using has a long text string which will use the word "Communication" in them, but they are not all necessarily the same string. The only one's I want to ignore are those that contain both "Communication" and "Information".

    So, for example:
    "Department of Communication", "Diploma in Communication" and "Level 4 Certificate in Communication" are all acceptable. "Department of Information and Communication" isn't because it contains the string "Information".

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Search A Range For Specific Text String

    The right answer to the wrong question!! A better answer (but also to the wrong Q from boopathiraja). Again, leave it with me.

  9. #9
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    I'm guessing this isn't possible?

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Search A Range For Specific Text String

    It's rare that something is impossible. In this case, it got forgotten about!! I'll take another look ASAP. Others may be able to sort you out in the meantime.

  11. #11
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    Not a problem I just thought it odd no-one else had responded over the last few days.

    Thanks.

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Search A Range For Specific Text String

    I may be lost.......differently......., but as I interpret, this formula in I2 and filled down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Results look like this:

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Subject
    Dept
    Subject
    Dept
    Subject
    Dept
    Subject
    Dept
    2
    Information and Communication
    1
    Communication
    1
    Progress
    2
    Development
    1
    1
    3
    Progress
    2
    Development
    2
    Progress
    1
    0
    4
    Development
    2
    Information and Communication
    2
    Progress
    2
    0
    5
    Information and Communication
    1
    Communication
    2
    Development
    2
    1
    6
    Communication
    1
    Development
    2
    1
    7


    Does this do what you want.......a net count?
    Last edited by FlameRetired; 11-10-2014 at 05:36 AM.

  13. #13
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    Quote Originally Posted by FlameRetired View Post
    I may be lost, too, but as I interpret, this formula in I2 and filled down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Results look like this:

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Subject
    Dept
    Subject
    Dept
    Subject
    Dept
    Subject
    Dept
    2
    Information and Communication
    1
    Communication
    1
    Progress
    2
    Development
    1
    1
    3
    Progress
    2
    Development
    2
    Progress
    1
    0
    4
    Development
    2
    Information and Communication
    2
    Progress
    2
    0
    5
    Information and Communication
    1
    Communication
    2
    Development
    2
    1
    6
    Communication
    1
    Development
    2
    1
    7


    Does this do what you want a net count?
    No, I need the formula to return the value in the column to the right of where "Communication" appears without the string "Information". So, for example, A6 meets the criteria therefore I need the formula in I6 to return the value in B6.

    Thanks anyway.

  14. #14
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Search A Range For Specific Text String

    HI Try this

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

  15. #15
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Search A Range For Specific Text String

    Hi, Refer the attachment... one more if condition was included to overcome resulting the first column if not found...
    Attached Files Attached Files

  16. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Search A Range For Specific Text String

    Interpreting another way in J2 and filled down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Results look like this:

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Subject
    Dept
    Subject
    Dept
    Subject
    Dept
    Subject
    Dept
    2
    Information and Communication
    1
    Communication
    1
    Progress
    2
    Development
    1
    1
    1
    3
    Progress
    2
    Development
    2
    Progress
    1
    0
    4
    Development
    2
    Information and Communication
    2
    Progress
    2
    0
    5
    Information and Communication
    1
    Communication
    2
    Development
    2
    1
    2
    6
    Communication
    1
    Development
    2
    1
    1

  17. #17
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    ProjectD1.xlsxThanks guys, these work.

    Now to test my luck. As an afterthought, what if I wanted to return other adjacent cells and not just the first adjacent column value?

    I've attached an extended spreadsheet example and in this, again A6 meets the criteria and R6 should return B6, S6 return C6 and so on for T6 and U6.

  18. #18
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Search A Range For Specific Text String

    Probably this,

    Apply this in r6 and drag it across

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

  19. #19
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Search A Range For Specific Text String

    the above formula assume that you will have a certain match, if no match found will return from column 1, if you want blanks if no match found need to include an criteria, clarify that

  20. #20
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    Quote Originally Posted by boopathiraja View Post
    the above formula assume that you will have a certain match, if no match found will return from column 1, if you want blanks if no match found need to include an criteria, clarify that
    Thanks, I do need blanks if no match found.

  21. #21
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Search A Range For Specific Text String

    hi, Apply this in R2 and drag it down and across

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by boopathiraja; 11-10-2014 at 08:19 AM. Reason: r2 changed from r1

  22. #22
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Search A Range For Specific Text String

    This in R2 and filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    Quote Originally Posted by boopathiraja View Post
    hi, Apply this in R2 and drag it down and across

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks but this works perfectly in the example spreadsheet I uploaded but, for some reason, when I input it to my actual spreadsheet it returns random blanks and #VALUE errors.

    Quote Originally Posted by FlameRetired View Post
    This in R2 and filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Again, this one works exactly as required when input to the uploaded example but when I input it to my real spreadsheet it only returns blank cells.

    I tried to simplify the example worksheet for ease as there are around 150+ columns on my actual real worksheet. When I've input both your formulas to my actual spreadsheet I have expanded the range for checking but it doesn't work.

    I've attached the actual worksheet I will be working on and the range I need to check for the "communication" and "information" criteria, as previously explained is Y:FU. I'd be grateful for an answer to this one as I'm really scratching my head now.
    Attached Files Attached Files

  24. #24
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Search A Range For Specific Text String

    Hi try this


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

  25. #25
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Search A Range For Specific Text String

    Hi, ref the attachment..
    Attached Files Attached Files

  26. #26
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    Quote Originally Posted by boopathiraja View Post
    Hi try this


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This works great with the example worksheet but when I add the formula to my actual spreadsheet it doesn't work on some rows but on others it returns data from the wrong cell or a zero where there should be an actual result.

    The file I've now attached is the actual spreadsheet I'll be working from with some sample data. The range to be searched is Y:VA.

    Sorry to be a pain.
    Attached Files Attached Files
    Last edited by xybadog; 11-17-2014 at 06:50 AM.

  27. #27
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Search A Range For Specific Text String

    Hi try this...

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

  28. #28
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    Quote Originally Posted by boopathiraja View Post
    Hi try this...

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Excellent, that seems to work exactly, thank you for your help...

    Can I ask, and sorry to be a pain, what if I want a simple search of the same range and return the same cell results? When I say simple search I mean a search that doesn't involve having to eliminate a specific string as above. So, for example:

    Search Y:VA for "Technology" and return the value in the cell to the right.

  29. #29
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Search A Range For Specific Text String

    Hi, try this...

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

  30. #30
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    365/2008
    Posts
    152

    Re: Search A Range For Specific Text String

    Apologies for resurrecting this thread after so long but I've been unable to respond till now.

    I've applied the formula above but, whereas it does work on MOST rows, in others it incorrectly returns an incorrect value this being the value in the first cell of the range (col Y) instead of the value it should return from the correct column.

    I'm at a loss as I have spent some time trying to identify a common denominator on the rows where this happens but I can't.

    In the attached spreadsheet I've entered the formula in "Report" worksheet and the "Raw" worksheet is the reference data. As an example, "Report" row 46 doesn't show the correct result. Col B (for example) should return Raw!BI45 but it returns Raw!Y45 instead which, coincidentally, is the first cell of the referenced range. Hope this makes sense.
    Attached Files Attached Files

+ 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. Search a string for a specific text value from cells
    By hockeyadc in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-25-2013, 08:39 AM
  2. Excel 2007 : Search specific text in a string
    By nlm in forum Excel General
    Replies: 7
    Last Post: 12-14-2011, 05:38 AM
  3. search for specific text pattern within string
    By nzdzy2 in forum Excel General
    Replies: 3
    Last Post: 10-10-2011, 11:47 AM
  4. Search for any Text String in specific range and copy + offset + move
    By FN2010 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-05-2010, 07:04 PM
  5. Search for specific text in a string and replace
    By VegasL in forum Excel General
    Replies: 5
    Last Post: 12-04-2009, 04:45 PM

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