Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

I want to find all cell values in column 'A' with, let's say a value "gmail.com" and replace all values of column B with "EN" (change the value in the same row of A and B result).

Is there a function that does this?

share|improve this question
Better suited to SuperUser – brettdj Dec 10 '12 at 10:08

migrated from stackoverflow.com Dec 10 '12 at 11:01

3 Answers

up vote 2 down vote
  • insert a blank column in column C
  • in C1 put =IF(A1="gmail.com","EN",B1) and copy down
  • copy and paste special value column C over the values in Column B
  • delete the working column C

For a partial search try something like
=IF(ISERROR(SEARCH("gmail.com",A1)>0),B1,"EN") in C1
then copy down etc

share|improve this answer
I did that but it said the formula contains an error. I have about 7000 rows in the excel is there any faster way, then copying? – Alex Dec 10 '12 at 10:16
You could use code but you will probably find a formula more adaptable without additional help. Can you provide a snapshot of you data higlighting where the formula is not working? – brettdj Dec 10 '12 at 10:19
Here's a screenshot (i48.tinypic.com/2s0efxw.jpg). First I just tested this formula for one value but it warned me with an error. I forgot to mention earlier that searching value should contain not match exact. What am I doing wrong? – Alex Dec 10 '12 at 10:31
up vote 1 down vote

Use an IF statement combined with a RIGHT function in column B. Place this formula in B1. 

=IF(RIGHT(A1,9)="gmail.com","EN", "HR")

This will look for the gmail.com and if it exists will populate the cell with EN, otherwise it will make it HR.

To copy it down all the rows in the column, double click on the bottom right corner of the cell where the drag cross is.

share|improve this answer
If you want to do this multiple times, do =IF(RIGHT(A1,9)="gmail.com", "EN", IF(RIGHT(A1,7)="msn.com", "MC", "HR")). You can unlimitedly nest this IF structure, but it will quickly get confusing – sinni800 Dec 10 '12 at 13:39
up vote -1 down vote

Use Sort & Filter.

Select column A and B (entirely) and apply the (auto)filter; select filter options on column A and chose text filters: contains. Set contains to "gmail.com", and apply filter. Column B will be filtered accordingly.

Change the first cell in column B, and copy it (Ctrl C). Then select the remaining cells in column B and press Ctrl V.

share|improve this answer
1  
They don't want to replace gmail.com in column A. They want to replace the value in column B based on gmail.com existing in column A. – CharlieRB Dec 10 '12 at 13:32

Your Answer

 
discard

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