Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Find specific letters in a specific range

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What should I do to find specific letters in a specific range and the get the cells that contain the letters I am looking for. Per example: A1 apple
    B1 orange
    C1 banana

    I want to know which cells contain the letter "e" (A1 and B1), get a textbox or something that let me know which cells contain the letter "e".

    Hugo

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use this formula

    Code:
    Function ContainsText(Rng As Range, Text As String) As String
    Dim T As String
    Dim Cll As Range
    For Each Cll In Rng
        If InStr(Cll.Text, Text) > 0 Then
            If Len(T) = 0 Then
                T = Cll.Address(False, False)
            Else
                T = T & "," & Cll.Address(False, False)
            End If
        End If
    Next Cll
    ContainsText = T
    End Function
    In VBA use it like

    Code:
    MsgBox ContainsText(Range("A1:A3"),"e")
    And in Excel, use like

    =ContainsText(A1:A3,"e")
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •