Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi
    This is my first time using the forum...

    How do i find if a cell contains one of many words?
    eg
    A1 = "Moon, star"
    A2 = "the cat jumped over the moon" B2=Look in A1 and if a word value is found and if so, present it ("Moon")

    Thanks
    Sean

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='smcilvin' post='762717' date='02-Mar-2009 03:29'][/quote]
    Welcome to the Lounge!

    There may be a clever way to do this using built-in functions only, but here is a user-defined VBA function you could use:
    Code:
    Public Function MatchWords(ByVal varList, ByVal varText) As String
      Dim arrWords
      Dim i As Integer
      Dim strRet As String
    
      varText = " " & varText & " "
      ' Split list on ", "
      arrWords = Split(varList, ", ")
      ' Loop through the words in the list
      For i = LBound(arrWords) To UBound(arrWords)
    	' If word occurs in text...
    	If InStr(1, varText, " " & arrWords(i) & " ", vbTextCompare) > 0 Then
    	  ' ... add it to strRet
    	  strRet = strRet & ", " & arrWords(i)
    	End If
      Next i
      If Not strRet = "" Then
    	' Remove first ", "
    	strRet = Mid(strRet, 3)
      End If
      MatchWords = strRet
    End Function
    Use it like this in B2:

    =MatchWords(A1,A2)

    See the attached sample workbook.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much Hans. I managed to get it to do what i need it to do (not a vb person!).
    Much appreciated!

    Regards, Sean

Posting Permissions

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