Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    In ccells A1:A10, I have various text (2 or more words in each cell). What I would like to do is percorm a function in the corresponding cell in column B if a certain text is in the respective cell in column A. For example, assume cell A1 contains the following: "red, blue, green" (sans the quotation marks of course). What I would like to do is add the contents of cells C1 and D1 if the word "red" is in cell A1. If "red" is not present, then insert a zero. Any ideas?
    Thanks in advance!

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='799559' date='23-Oct-2009 12:19']In ccells A1:A10, I have various text (2 or more words in each cell). What I would like to do is percorm a function in the corresponding cell in column B if a certain text is in the respective cell in column A. For example, assume cell A1 contains the following: "red, blue, green" (sans the quotation marks of course). What I would like to do is add the contents of cells C1 and D1 if the word "red" is in cell A1. If "red" is not present, then insert a zero. Any ideas?
    Thanks in advance![/quote]
    Try the following in cell B1 and fill down to B10
    =IF(ISERROR(FIND("red",A1)),0,C1+D1)
    Regards
    Don

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Don, works like a charm!

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='799567' date='23-Oct-2009 13:00']Thanks Don, works like a charm![/quote]

    If there's a chance that your searched-for word could be found embedded in another word (The wall is coloured green.), returning the wrong answer, I'm afraid that a UDF will be required. This carries a speed penalty. Let us know if this is a possibility.
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='799567' date='23-Oct-2009 13:00']Thanks Don, works like a charm![/quote]
    If you want to test for whole words only use the folllowing formula in B1

    =IF(Contains_Word(A1,"red",FALSE),C1+D1,0)

    The first argument (A1) identifies the cell which is being tested.
    The second argument ("red") identifies the word which is being tested for.
    The third argument (False) identifies whether to make the search case sensitive.

    and place the following code in a standard module

    This post and code have been revised to accommodate case insensitive searching.
    [codebox]
    Public Function Contains_Word(Target As Range, SearchFor As String, CaseSensitive As Boolean) As Boolean
    Dim Posn As Long
    Dim Tail As Long
    Dim Head As Long
    Dim tgt As String
    If CaseSensitive Then
    tgt = Target
    Else
    tgt = UCase(Target)
    SearchFor = UCase(SearchFor)
    End If
    Posn = InStr(1, tgt, SearchFor)
    Do While Posn > 0
    On Error Resume Next
    Head = Posn - 1
    Head = Asc(Mid(tgt, Head, 1))
    Tail = Posn + Len(SearchFor)
    Tail = Asc(Mid(tgt, Tail, 1))
    On Error GoTo 0
    Select Case Posn
    Case 1
    Select Case Tail
    Case Is < 48, 58 To 64, 91 To 96, Is > 122
    Contains_Word = True
    Exit Do
    End Select
    Case Len(tgt) - Len(SearchFor) + 1
    Select Case Head
    Case Is < 48, 58 To 64, 91 To 96, Is > 122
    Contains_Word = True
    Exit Do
    End Select
    Case Else
    Select Case Tail
    Case Is < 48, 58 To 64, 91 To 96, Is > 122
    Select Case Head
    Case Is < 48, 58 To 64, 91 To 96, Is > 122
    Contains_Word = True
    Exit Do
    End Select
    End Select
    End Select
    Posn = InStr(Posn + 1, tgt, SearchFor)
    Loop
    End Function

    [/codebox]
    Regards
    Don

Posting Permissions

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