Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to find all cell values in column 'A' with, let's say a value "" 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

migrated from Dec 10 '12 at 11:01

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

  • insert a blank column in column C
  • in C1 put =IF(A1="","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("",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 ( 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

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

=IF(RIGHT(A1,9)="","EN", "HR")

This will look for the 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)="", "EN", IF(RIGHT(A1,7)="", "MC", "HR")). You can unlimitedly nest this IF structure, but it will quickly get confusing – sinni800 Dec 10 '12 at 13:39

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 "", 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
They don't want to replace in column A. They want to replace the value in column B based on existing in column A. – CharlieRB Dec 10 '12 at 13:32

You must log in to answer this question.