# Solved If text word Then change to numerical value

June 30, 2015 at 07:54:22
Specs: Windows 7
 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.

See More: If text word Then change to numerical value

#1
June 30, 2015 at 09:59:16
 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 Software forum.MIKEhttp://www.skeptic.com/

Report •

#2
July 1, 2015 at 08:55:16
 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))message edited by DerbyDad03

Report •

#3
July 1, 2015 at 10:29:16
 DerbyDad03Your formula:=IF(A1="N/A",3,CHOOSE(LEN(A1),,1,0,,2))Returns the error message #VALUE if cell A1 is nullA 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 zeroANY two letter word will return oneANY five letter word will return twoANY 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,""))))MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
July 1, 2015 at 11:29:36
 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.

Report •