Thread: if cell contains a specific text, then put that specific text into a different cell

I am trying to create a formulae that searches a cell for a specific text (word), and then if it finds that text, that same text is put into a different cell.

Then I am wanting this formulae to search for a number of different texts (words) in a single cell, and for every one of those text (words) that are found, that same text (word) is put into the one/same different cell.

For example,

cell A3 (contains the following words)
risk
box
cat
mouse
hat

Formulae to search cell A3, for "risk" and "cat" and "hat"

cell B5 (formulae puts words into cell B5, separated by commas and spaces)
risk, cat, hat

Any ideas would be appreciated

This won't be pretty if you dumbed that question down too far, but this is usable so far...

B5: =SUBSTITUTE(TRIM(IF(ISNUMBER(SEARCH("risk", A3)), " risk", "") & IF(ISNUMBER(SEARCH("cat", A3)), " cat", "") & IF(ISNUMBER(SEARCH("hat", A3)), " hat", "")), " ", ", ")

Awesome. Thanks very much. It works !!!!!!!! The things that I don't know?? I was trying with similar formulae but did not use the &, SUBSTITUTE< TRIM - very good!!

I used word strings - e.g. instead of "risk", I used "risk management". What happened was that it found and placed the word "risk management" in cell B5, but it separated the words with a comma - i.e. "risk, management" ?? I am actually wanting it to return "risk management" If you know how to correct, it would be greatly appreciated

If I could trouble you/others a little further. I would love to know how to do the following - same situations but looking for a way to automate a little more.

That is,

cell A3 (contains the following words)
risk
box
cat
mouse
hat

Formulae to search cell A3, for "risk" and "cat" and "hat" and about 40 more words

cell B5 (formulae puts words into cell B5, separated by commas and spaces)
risk, cat, hat

So, one way of doing this is to just keep repeating the "&IF(ISNUMBER ...." for all the different words. This would make the formulae very long and also if I made a change to a word, I would have to redo the formulae

So, wondering if there is a way of having the 40 or more words to be in one cell, from which the formulae would pic the words risk, cat, hat and the other 40 words, and search cell B5 to produce - "risk, cat, hat" or any of the other 40 or so words that matched.
This method would also mean that a change(addition, deleting) in a word could be managed by adding that word to the 'one cell' rather than re-doing the formulae.

Any more thoughts much appreciated?

The original question is fixed to multiple word with another trick like this:

=SUBSTITUTE(SUBSTITUTE(TRIM(IF(ISNUMBER(SEARCH("risk management", A3)), " risk|management", "") & IF(ISNUMBER(SEARCH("cat", A3)), " cat", "") & IF(ISNUMBER(SEARCH("hat", A3)), " hat", "")), " ", ", "), "|", " ")


I still think the examples with words like "cat" and "hat" are misguided. Surely "risk management", "return on investment", "capitalization".... strings that match your actual strings are worth putting into the original question? When you need 40, the suggestion above is simply unwieldy.

What you're doing is called STRING CONCATENATION and it is not really a builtin Excel function. But there are many ready-to-use VBA solutions for adding this capability into your workbook. You would have to save your workbook as .xlsm, but the end result would be much more friendly to use.

Are you interested in a VBA solution? I'm assuming in the end you will be searching MULTIPLE cells looking for the 40+ words? If so, no more make believe examples. Please do the following.

1) Create a workbook with the list of the actual 40 words (or phrases) in a single column
2) Include the actual layout of your real workbook showing the multiple examples of real strings you would want to search
3) If there will be more than one long string to search, show how that actually looks in your real workbook and where you'd want the answers to appear for each searched string.

Thanks for your suggestion and assertiveness.

In relation to the attached workbook,

I am wanting the formulae to do the following:

1. Search Data EntryI4 for any words that are contained in 'Roles' worksheet; If match is made, then for each matched word, the word is to be put into Data EntryC4
2. Search Data EntryI4 for any words that are contained in 'Categories' worksheet; If match is made, then for each matched word, the word is to be put into Data EntryD4
3. Do not want it to be case sensitive; Do not want the same word repeated more than once in the one cell

Then this sequence is to be repeated for Data EntryI4 to I319 and Data EntryC4 to C319 and Data EntryD4 to D319

Well, I wrote you a new FUNCTION that works a lot like SUMIF(), you can actually put this formula into C10 and it would work:

=SEARCHSTRINGS($I10, Roles!$A:$A, ",", TRUE)

....that would give you all the Roles that matched. But this new function is very calc intense... when I copied it to the entire column C the sheet screeched to a halt. I thought it was better to let a macro use the function to enter the value for you in the column C cells as flat values. So I added some macros and buttons to call them. You have to click the buttons to update the columns, they won't update themselves.

Option Explicit

Sub AddRoles()
Dim ROLErng As Range, r As Range

    Set ROLErng = Range("I:I").SpecialCells(xlConstants)
    
    For Each r In ROLErng
        r.Offset(, -6).Value = SEARCHSTRINGS(r, Sheets("Roles").Range("A:A").SpecialCells(xlConstants), ",", True)
    Next r

    Range("C3") = "3. Select Role"
End Sub

Sub AddCategories()
Dim CATEGrng As Range, c As Range

    Set CATEGrng = Range("I:I").SpecialCells(xlConstants)
    
    For Each c In CATEGrng
        c.Offset(, -5).Value = SEARCHSTRINGS(c, Sheets("Categories").Range("A:A").SpecialCells(xlConstants), ",", True)
    Next c

    Range("D3") = "4. Select Category"
End Sub

Function SEARCHSTRINGS(cel As Range, rngTBL As Range, _
    Optional Delim As String, Optional NoDUPE As Boolean) As String
    Dim r As Range, BUF As String

Set cel = cel.Cells(1)          'in case more than one cell is put in first parameter
If Delim = "" Then Delim = ","

    For Each r In rngTBL
        If InStr(cel, r) > 0 Then
            If NoDUPE Then
                If InStr(BUF, r) = 0 Then BUF = BUF & r & ", "
            Else
                BUF = r & ", "
            End If
        End If
    Next r
    
    If BUF <> "" Then
        SEARCHSTRINGS = Left(BUF, Len(BUF) - 2)
    Else
        SEARCHSTRINGS = "none found"
    End If

End Function

The stupid forum won't let me attach a file, something's broke.

Here's a link to my file drop box on my code site. Your file is down in the USER FILES.

FILES