If someone enters "yes" "no" "maybe" "N/A" in cell A1 of Sheet1, I want each one of those words to be converted to a number (0,1,2,3) respectively in Sheet2.

You posed your question in the VB.NET/C# forum,

but it would appear to be an Excel question.If it is indeed an Excel question,

then what you need is a Nested IF() formula.

Try this formula in a cell on your Sheet 2:=IF(Sheet1!A1="yes",0,IF(Sheet1!A1="no",1,IF(Sheet1!A1="maybe",2,IF(Sheet1!A1="N/A",3,))))

And in the future, Excel questions are best asked in the

Office Softwareforum.MIKE

A shorter option to consider, assuming you are restricting the entries in the cell to those 4 choices: =IF(A1="N/A",3,CHOOSE(LEN(A1),,1,0,,2))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

DerbyDad03 Your formula:

=IF(A1="N/A",3,CHOOSE(LEN(A1),,1,0,,2))

Returns the error message #VALUE if cell A1 is null

A small modification corrects that problem.=IF(A1="","",IF(A1="N/A",3,CHOOSE(LEN(A1),"",1,0,,2)))

BUT, the other down side of your formula is:

ANY three letter word will return zero

ANY two letter word will return one

ANY five letter word will return two

ANY six or greater letter word returns a #VALUE errorAlso, in my original formula, it will return 0 if cell A1 is null so a small modification:

=IF(Sheet1!A1="yes",0,IF(Sheet1!A1="no",1,IF(Sheet1!A1="maybe",2,IF(Sheet1!A1="N/A",3,""))))

MIKE

re: Returns the error message #VALUE if cell A1 is null"While your modification is certainly a quick and easy fix, it is also possible to prevent A1 from ever being empty. By employing those methods, the #VALUE error can be prevented. Depending on the criticality of the entry in A1, and the absolute need for a 0 - 3 on Sheet 2 (i.e.

IF(A1="",""is not an acceptable result) there may be a need to prevent A1 from ever being empty.re:

"BUT, the other down side of your formula is..."As I noted in my response:

"...assuming you are restricting the entries in the cell to those 4 choices".Since the OP supplied 4 choices of input and 4 corresponding outputs - with no stipulation of what he wanted to return for any other entry - I offered a formula that met those criteria. As you know, Data Validation can prevent the entry of any other values.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History