+ Reply to Thread
Results 1 to 7 of 7

Find occurance of text in a column

  1. #1
    Forum Contributor
    Join Date
    06-11-2009
    Location
    Cape Town
    MS-Off Ver
    Microsoft 365
    Posts
    818

    Find occurance of text in a column

    I have 2 workbooks. In workbook (1), I have a column with a few thousand store names. In workbook (2), I have a column with a few hundred store names. The store names are not identical but have the following unique words/numbers: ABC123 etc. I want to identify if a store in workbook (2) exists in workbook (1). I have attached a sample file.
    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: Find occurance of text in a column

    Select B2

    Conditional Formatting
    New Rule
    Use a formula to determine...

    =NOT(ISNA(VLOOKUP(A1,B$1:B$8,1,0)))

    Format as green
    Use Format painter (paintbrush icon) to copy to other cells
    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.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Find occurance of text in a column

    You would need to use Conditional Formatting to do that.

    Select column A, go CF, New Rule, and choose Use a formula to determine...

    Enter formula:

    =SEARCH(C$2,A2)>=1

    Select formatting in click Ok.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find occurance of text in a column

    In CF rules

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


    How can you use Conditional Formating.

    --In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. See here how you can work using CF.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

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

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,602

    Re: Find occurance of text in a column

    Select b2:b8
    and use below formula in condition format and change format as per your requirement
    =ISNUMBER(SEARCH(" "&C$2&" "," "&B2&" "))*1
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Find occurance of text in a column

    I looked at this a little differently. Seeing that there are a few hundred names to be considered, I assumed that there would be maybe a few hundred unique numbers or words to be compared with.

    For workbook 2 I used this formula to draw the names from workbook 1 that matched the keywords or numbers in workbook 2 with the results entered in column H
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I then used this formula in conditional formatting to highlight the names that matched the keywords or numbers in workbook 2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Forum Contributor
    Join Date
    06-11-2009
    Location
    Cape Town
    MS-Off Ver
    Microsoft 365
    Posts
    818

    Re: Find occurance of text in a column

    Thanks very much to all for your response. There are a few hundred unique numbers or words and newdoverman's solution works perfectly.

+ 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. count occurance of particular name or text in column
    By meetvivek72 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2013, 07:38 AM
  2. Array: Find first occurance of a name
    By Lensmeister in forum Excel General
    Replies: 11
    Last Post: 07-10-2011, 12:00 PM
  3. [SOLVED] Find next occurance
    By Jambruins in forum Excel General
    Replies: 5
    Last Post: 08-10-2006, 11:55 AM
  4. [SOLVED] Find last occurance of character in text string
    By JDay01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2006, 12:35 PM
  5. Find last occurance of text in range
    By farutherford in forum Excel Formulas & Functions
    Replies: 68
    Last Post: 09-06-2005, 09:05 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