Replace values in Excel

Question

At work, we have a spreadsheet that the data is copied from another uploaded spreadsheet. We need to convert:

Yes --> 1

No --> 0

NA --> 9

Is there any way to create a formula that will automatically do that?

In place. We usually copy and paste the YES and NO responses and later use Find and Replace to change.. — Justin Hanlon
– Justin Hanlon, Commented Dec 13, 2017 at 19:48

Dale Kube · Accepted Answer · 2017-12-13 19:23:51Z

1

=if(Cell="Yes",1,if(Cell="No",0,9))

Replace "Cell" with the cell you are referencing. You can drag this formula down and apply it to all cells in the given column. This assumes that your column or row can only take on the three listed values: "Yes", "No", and "NA". You'll need to nest more "if" functions to account for the addition of new values.

answered Dec 13, 2017 at 19:23

Dale Kube

1,4702 gold badges15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replace values in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related