+ Reply to Thread
Results 1 to 6 of 6

Searching for a string of text within a longer string of text within an entire column

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Searching for a string of text within a longer string of text within an entire column

    Hello,

    I just posted a very similar problem, but now require a different solution for this problem. I am searching for a string of text within a longer string of text. I am trying to return a "Found" if the text is found in any format anywhere in the column e.g. a "Found" would be returned for the word orange even if the other words were variations on it e.g. "orange,"; "three oranges"; "oranges&".

    If the word is not found within the cell, it will return a "Not Found".

    I have provided an example in the attached spreadsheet.

    Any help much appreciated!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,744

    Re: Searching for a string of text within a longer string of text within an entire column

    Here is a VBA solution.

    Option Explicit
    Option Compare Text
    
    Sub FindText()
        Dim crit As String, s As String, x As String
        Dim i As Long, j As Long, lr As Long, lr2 As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
        lr2 = Range("C" & Rows.Count).End(xlUp).Row
        For j = 2 To lr2
            s = ""
            x = ""
            crit = Range("C" & j)
            For i = 2 To lr
                If InStr(Range("A" & i), crit) > 0 Then
                    Range("D" & j) = "Found"
                    x = Range("A" & i).Address
                    s = s & " ," & x
                    Range("F" & j) = s
                End If
            Next i
        Next j
    End Sub


    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Searching for a string of text within a longer string of text within an entire column

    You need wildcards on the criteria value

    =IF(COUNTIF(A:A,"*"&C2&"*"),"Found","Not Found")

  4. #4
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Re: Searching for a string of text within a longer string of text within an entire column

    Thanks guys. The wildcard solution worked.

    Thanks to both of you for your assistance.

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Searching for a string of text within a longer string of text within an entire column

    You're welcome.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,744

    Re: Searching for a string of text within a longer string of text within an entire column

    You are welcome. Thanks for the Rep

+ 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. Replies: 1
    Last Post: 08-05-2017, 12:06 AM
  2. Replies: 2
    Last Post: 05-27-2016, 12:30 AM
  3. [SOLVED] Finding a specific text string throughout an entire column and if there return true/false
    By myfivemonkeys in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-06-2015, 07:31 PM
  4. [SOLVED] How to extract text strings out of longer text string
    By GIS2013 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-17-2013, 06:08 AM
  5. [SOLVED] Find partial text string within another text string return original text into cell.
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-17-2013, 02:58 PM
  6. [SOLVED] Macro to Search column for Text string and Copy Entire Row to another Worksheet
    By TJacks1908 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-09-2013, 07:11 AM
  7. [SOLVED] Extract strings from a longer text string
    By Frostyvegi in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-26-2012, 01:44 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