Solved

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

Posted on 2011-02-14
12
266 Views
Last Modified: 2012-05-11
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
Comment
Question by:yvan_vallee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 34893190
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
 

Author Comment

by:yvan_vallee
ID: 34893222
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
 
LVL 2

Expert Comment

by:geoffkk
ID: 34893463
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 22

Expert Comment

by:rspahitz
ID: 34893475
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
 

Author Comment

by:yvan_vallee
ID: 34897300
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 34898167
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 34898253
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
 

Author Comment

by:yvan_vallee
ID: 34898704
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 34898730
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
 
LVL 22

Accepted Solution

by:
rspahitz earned 250 total points
ID: 34898740
Usage of the above function:

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

Author Closing Comment

by:yvan_vallee
ID: 34899507
Thank you that works fine and to provide the VB script code as I am not familar much with it.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34899558
Glad to help.  Excel functions can be a wonderful thing :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question