+ Reply to Thread
Results 1 to 7 of 7

Find part of the text in a range of cells

Hybrid View

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Find part of the text in a range of cells

    Hello everybody
    I have some values (numbers and texts) in range("A1:A10")
    I have a code that show me an inputbox to type a value in and according to the value if it is found the address will be put next to the cell ..
    My request is to be able to find for example all the occurences of specific text (say I typed "Yass" then the code has to give me three addresses in B1 & B6 & B10 .In other words to search part of the text not the whole text
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,550

    Re: Find part of the text in a range of cells

    Something like this?
    Sub FindVBA()
        Dim r As Range
        Dim c As Range
        Dim s As String
        Dim ms As String
        Set r = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)     ', Range("A65536").End(xlUp))
    
        ms = "The Value was found at  "
        s = InputBox("Type Anything To Find", "Find VBA")
        
        For Each c In r
            If c Like "*" & s & "*" Then c.Offset(0, 1) = c.Address
        Next c
    End Sub

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Find part of the text in a range of cells

    Great Mr. jolivanes
    What about small letters .. I want to get the same results if I typed (yass) for example ...?

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,641

    Re: Find part of the text in a range of cells

    Other method
    Sub test()
        Dim txt, x
        txt = InputBox("Enter")
        x = Len(txt)
        [b1:b10] = Evaluate("if(left(a1:a10," & x & ")=""" & txt & """,a1:a10,"""")")
    End Sub

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Find part of the text in a range of cells

    thanks Mr. Jindon for this simple and effective code ..
    Just a point it returns the value itself .. I want to return the address of the value

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,641

    Re: Find part of the text in a range of cells

    How do you want it?
    [b1:b10] = Evaluate("if(left(a1:a10," & x & ")=""" & txt & """,address(row(a1:a10),2,4),"""")")
    or
    MsgBox Join(Filter(Evaluate("transpose(if(left(a1:a10," & x & ")=""" & _
        txt & """,address(row(a1:a10),2,4),char(2)))"), Chr(2), 0), vbLf)

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Find part of the text in a range of cells

    Thanks a lot Mr. Jindon
    You are very helpful
    I'm learning a lot from you my tutor

+ 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. Formula to identify part of a text in cell from range of cells & insert adjacent cell text
    By Novicebutnotforlong in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-19-2013, 02:11 AM
  2. Replies: 2
    Last Post: 09-07-2013, 07:50 PM
  3. Replies: 9
    Last Post: 07-03-2013, 07:39 AM
  4. [SOLVED] Find Text In Range With Part Of Text & Return True or False
    By tis28 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-30-2013, 09:35 PM
  5. Replies: 7
    Last Post: 01-24-2013, 06:55 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