+ Reply to Thread
Results 1 to 14 of 14

Identify specific text strings in a cell?

  1. #1
    Registered User
    Join Date
    05-20-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Identify specific text strings in a cell?

    Hi everyone,

    Stuck with a small query regarding identifying specific text string in a cell filled with text.

    e.g. cell A2 = alpha, bravo, charlie, delta, eta, phi [single instances
    cell A3 = alpha delta phi eta [single instance]
    cell b1=alpha
    cell b2=bravo etc.

    Identification should be in the following format:

    Excel.jpg

    I've tried using the formula = [len(A2)-len(substitute(A2, b1, "")]/len(B1)

    But have been getting haywire results.

    Can I get some help with this asap?

    Mods: please shift/merge this thread in case I've posted it in a wrong section.
    Attached Images Attached Images

  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: Identify specific text strings in a cell?

    Quote Originally Posted by mnidevil View Post
    cell A2 = alpha, bravo, charlie, delta, eta, phi
    cell A3 = alpha delta phi eta
    Hi.

    So some of your entries are space-delimited, and some comma-delimited?

    I think it might be useful you posting an actual workbook, rather than pictures.

    Regards
    Click * below if this answer helped

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

  3. #3
    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,895

    Re: Identify specific text strings in a cell?

    tRY THIS OUT:
    Attached Files Attached Files
    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

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Identify specific text strings in a cell?

    in B2
    =NOT(ISERR(SEARCH(B$1,$A2)))+0

    copy across and down as far as I4
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  5. #5
    Registered User
    Join Date
    05-20-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Identify specific text strings in a cell?

    Excel Query.xlsx

    @XOR LX for your reference. Can't attach the actual file due to confidentiality issues.

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

    Re: Identify specific text strings in a cell?

    Thanks. So can you confirm that the strings are always only ever comma-separated, not e.g. space-separated, as in some of the original examples you gave?

    @Glenn Kennedy, Special-K

    You have to be careful in this type of question. You do not necessarily want, for example, a string such as:

    "alpha, bravo, charlie, xeta"

    to register a score for "eta". This is why the delimiter (e.g. space, comma) is an important part of any solution.

    Regards

  7. #7
    Registered User
    Join Date
    05-20-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Identify specific text strings in a cell?

    Last entry will not have a delimiter

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

    Re: Identify specific text strings in a cell?

    Quote Originally Posted by mnidevil View Post
    Last entry will not have a delimiter
    Thanks, but what about my question re the type of delimiter?

    Regards

  9. #9
    Registered User
    Join Date
    05-20-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Identify specific text strings in a cell?

    Oops, missed that. All entries will have comma as the delimiter always.
    Last edited by mnidevil; 12-18-2014 at 12:35 PM.

  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,895

    Re: Identify specific text strings in a cell?

    I noticed the zeta/eta issue whn the OP put up their actual sheet... I also see that in the posted sheet, the last entry DOES have a comma delimiter...

  11. #11
    Registered User
    Join Date
    05-20-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Identify specific text strings in a cell?

    Last entry will not have a delimiter. I posted only part of the entire cell text as it had 193 separate strings seprated by comma. Missed the absence of delimiter at the end.

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

    Re: Identify specific text strings in a cell?

    In C3:

    =N(ISNUMBER(SEARCH(", "&C$2&", ",", "&$B3&", ")))

    Copy across and down.

    Regards

  13. #13
    Registered User
    Join Date
    05-20-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Identify specific text strings in a cell?

    Thanks. Works fantastic!!

    Can you please explain why you used isnumber & N in the formula?

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

    Re: Identify specific text strings in a cell?

    You're welcome!

    SEARCH returns either the position of the search string within the searched string, if found, or a #VALUE! error.

    ISNUMBER returns TRUE if the value passed to it is a number (which is precisely what the return of the SEARCH function will be, assuming the search string is found), and FALSE otherwise.

    N converts, amongst other things, Boolean TRUE and FALSE returns to 1 and 0 respectively.

    Regards

+ 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. VBA code to identify the cell number in a column which contains a specific text
    By rshnkmr39 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2014, 03:38 AM
  2. Replies: 1
    Last Post: 05-09-2012, 01:30 PM
  3. Replies: 5
    Last Post: 05-03-2011, 09:35 AM
  4. Identifying specific text strings in a range
    By steekvey in forum Excel General
    Replies: 2
    Last Post: 05-01-2008, 08:00 AM
  5. Replies: 3
    Last Post: 09-07-2007, 02:25 AM

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