Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using Excel, and am running into a problem that leads me to believe there might be a macro or other solution for this. Hand editing is tedious! :)

I am looking use the find/replace (or something else) tool in excel to do the following:

  1. find a word in some nth row (the word will be found in column 1 of that row)
  2. put a new (previously defined) word in the same row, but into the cell for column 2

Is this possible?

Thanks, jml

share|improve this question

migrated from stackoverflow.com Nov 24 '11 at 13:24

This question came from our site for professional and enthusiast programmers.

    
So you want to put some data into column 2 for each row that has some other data that is found in column 1? –  cheeken Sep 9 '11 at 23:59
    
yes, precisely. –  jml Sep 10 '11 at 0:03
    
Are the words you're looking for part of some larger text, or by themselves? –  Tim Williams Sep 10 '11 at 0:04
    
part of larger text, but only within the first column's cells (aka, the word i am looking for might be "abc_", but the text in the found cell could be "abc_defg") –  jml Sep 10 '11 at 0:08
    
Does column2 start out empty? Are you only looking for a single word or do you have a list? A macro or user-defined function might be easier to implement, depending on your exact needs. –  Tim Williams Sep 10 '11 at 3:41
add comment

1 Answer

up vote 3 down vote accepted

Is there some reason a formula won't work?

You can use this formula in the second column (starting with B1): IF(ISERR(FIND("your_search_string",A1)),"replace_value_if_not_found","replace_value_if_found"), then Fill Down. If you have multiple "find/replace" values, you can next them in a third argument of the if.

If, once you've done this, you would rather have actual values in the cells instead of formulas, copy the entire column, then do Paste Special on the same column and select Values (instead of default/Formulas).

share|improve this answer
    
B1, then fill down. –  cheeken Sep 10 '11 at 0:15
    
hm; this sort of works, but if it's false, I get a "FALSE" printed into the cells in which it fails. –  jml Sep 10 '11 at 0:20
    
Edit - Please see my amended answer. –  cheeken Sep 10 '11 at 0:22
    
this won't work if i already have a value in the B1 cell. it simply overwrites the value with the function. –  jml Sep 10 '11 at 0:29
    
Yes, it does - I thought you wanted to replace. If not, what do you want to do? Append, prepend, etc.? –  cheeken Sep 10 '11 at 0:32
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.