• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Excel need to copy a cell if not containing one or more keywords

I need to check a column for particular keywords (more than one) , if found I need to change the content of that cell in a new column if not to just copy it to the new column.

I was hoping to use the IF and FIND statement to do this but if if the FIND fails than it puts #VALUE! in the corresponding cell in the new column.
0
yvan_vallee
Asked:
yvan_vallee
  • 7
  • 4
1 Solution
 
rspahitzCommented:
VLOOKUP can often be used for searching for specific cell value in more than one cell.  If each cell contains one keyword, that may be the way to go.

Can you describe your problem a bit more?

It sounds like you need IF(VLOOKUP(keyword1,...),[add to cell next to result], VLOOKUP(keyword2,...))
0
 
yvan_valleeAuthor Commented:
I want to have a column that checks another column and fills it with either the content of the original column OR if the column contains a keyword to put a default value ( to replace the contant of the original column).

I belive a VLOOKUP is more to check if it matches a (look-up) table content first column. I don't want to do a look-up table (unless there is no choice). It is really to change the cell content in the new column if it matches the keyword checking (if not simply copy the original content).

Thank you,
Yvan
0
 
geoffkkCommented:
I will assume the cell containing a keyword has just the keyword, ie not a keyword in part of a string.
Try
create a list of the keywords in a suitable place, eg other worksheet. Name the range kword.
If the data to be checked in in column A starting at A3, use the following formula in B3:
=IF(ISNA(VLOOKUP(A3,kword,1,FALSE)),A3,"default")
where "default" is whatever you want to put there.

If you wanted a different default value for each keyword you could use snother column in the keyword table and replace "default" by vlookup(A3,kword,2,false)
Geoff
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rspahitzCommented:
so it still sounds something like this:

=IF(ISERR(VLOOKUP(keyword, KeyWordList, 1, FALSE)), originalvalue, keyword))

KeyWordList can be set up as a named range of keywords somewhere.

So if you have your original value in column A (original value), put this formula in column B, and reference the keyword (maybe from column C).

How many keywords do you want to match for each cell?  If more than 3, then this will be tough to code so maybe that's why you want to go a different way?  If so, then VBA/macros would be the way to go.
0
 
yvan_valleeAuthor Commented:
Hi,
Just to clarify here, the keyword is part of a text in the cell. So I was hoping that the function FIND could be use part of the cell formula to check for that keyword part of the cell.

Is it possible to add FIND in the formula so it can check the cell content for the keyword?

Thank you.
0
 
rspahitzCommented:
The VLOOKUP won't work with FIND (as far as I know) and the FIND won't work on multiple cells.

One solution, if you have a fixed range of cells containing keywords, is to combine the keywords into a single cell (using the CONCATENATE function), with a delimiter that won't appear in any of the text, such as "|".  Then the FIND will work.

E.g.
if D1:D7 contain keywords
and C1 contains some default word
and B1 contains the word you want to see if it's a keyword

set cell E1=CONCATENATE("|", D1, "|", D2, "|", D3, "|", D4, "|", D5, "|", D6, "|", D7, "|")
set cell A1=IF(FIND("|" & B1 & "|", $E$1 & B1 & "|") > LEN(D1), $C$1, B1)

What this does is combines all the keywords into a single cell E1, with separators.
And A1 will try to find the keyword in B1 inside that list of keywords; if found, it will use the keyword otherwise it will use the default.

...or are you looking for the opposite: to see if any keyword is located anywhere within a sentence?
If so, you'll need some VBA or a more complex arrangement of cell (one per sentence) to indicate whether each keyword is located in cell and if so then mark it, then vlookup for the first one that matches and show it.
0
 
rspahitzCommented:
Here could be a sample custom function that might handle what you're trying to do.

Put the code below into a new VBA module (from Excel, Alt-F11 then menu Insert | Module)

 
Function FindKeyword(KeywordRange As Range, SentenceToCheck As String, DefaultValue As String) As String
    Dim strResult As String
    Dim objCell As Range
    
    strResult = DefaultValue
    For Each objCell In KeywordRange
        If InStr(SentenceToCheck, objCell.Value) > 0 Then
            strResult = objCell.Value
            Exit For
        End If
    Next objCell
    
    FindKeyword = strResult
End Function

Open in new window


Then back in Excel, add a formula such as this:

=FindKeyword(A1:F1,H1,"not found")

Where A1:F1 is the range of keywords (or maybe C1:C7, etc)
and H1 is the sentence that may contain a keyword
and "not found" is the default text or a cell with that text.
0
 
yvan_valleeAuthor Commented:
Hi,
This (I used the VB script) is "almost" doing what I need to do. It does return the keyword in the cell with the formula if the tested cell contains one of the keywords.

For me, the ultimate solution is to replace this keyword with another name like if it would be a VLOOKUP. So finding a keyword in the tested cell , if found, to substitute it with a another word or string in the cell having the formula. I hope this is clear enough.

Is this possible to do in the VB script or in the cell formula itself?

Thank you!
0
 
rspahitzCommented:
I guess you can add an extra parameter to the function to indicate what you'd like to show if found.
Function FindKeyword(KeywordRange As Range, SentenceToCheck As String, DefaultValue As String, SubstituteValue As String) As String
    Dim strResult As String
    Dim objCell As Range
    
    strResult = DefaultValue
    For Each objCell In KeywordRange
        If InStr(SentenceToCheck, objCell.Value) > 0 Then
            strResult = SubstituteValue
            Exit For
        End If
    Next objCell
    
    FindKeyword = strResult
End Function

Open in new window

0
 
rspahitzCommented:
Usage of the above function:

=FindKeyword(A1:F1,H1,"not found", "found keyword!")
0
 
yvan_valleeAuthor Commented:
Thank you that works fine and to provide the VB script code as I am not familar much with it.
0
 
rspahitzCommented:
Glad to help.  Excel functions can be a wonderful thing :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now