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

Report •


#1
June 30, 2015 at 09:59:16
✔ Best Answer
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.

MIKE

http://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))

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

message edited by DerbyDad03


Report •

#3
July 1, 2015 at 10:29:16
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 error

Also, 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

http://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.

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


Report •


Ask Question