Help for the beginner please - converting text to a value

[Michael Driscoll]

Hi, Just starting out so this may be easy.  If a cell contains text and I want to convert the text phrase to a number, how do I do that?  Here is the table:

If the phrase in column E is "Moderately-to-severely impaired", I want to convert the corresponding value in column C to 0.1.  IF the phrase in column E is "Severely impaired" then I want the corresponding value in column C to be 0.9.  Thank you. 

[#2]

Hello,

You can build a lookup table with all the different categories and their corresponding values. Then you can us a Vlookup() function to look up the value. Consider this screenshot:

The formula in cell C2 is =VLOOKUP(E2,$L$1:$M$6,2,FALSE) and copied down. I put in some dummy numbers in the lookup table, which sits in column L and M. The lookup table can be on a different sheet, of course.

[#3]

Add a formula in Column B or C: =if(E2="Moderately...",0.1,if(E2="Severely...",0.9,0))

[#4]

No, please don't. A nested IF is definitely NOT the way to go.

Why?

Well, the formula is really hard to write and harder to maintain. 

If the parameters change and "Moderately" now needs to return "0.125" instead of "0.1", then you have to find EACH formula with the nested IFs and you need to change EACH formula accordingly. That is very poor data architecture.

With a lookup table and a Vlookup function, you only need to change ONE cell if the parameters for "Moderately" change. One cell. And all the Vlookup formulas will return the correct result. 

Nested IFs are NOT a good way to solve this issue.

[#5]

I think I did not understand the question. If you only have two conditions: the nested IF is a very effective and simple solution. if you have more values and you need to nest more IF statements... I agree IF is not a good solution. However your reasons are not very valid: You always can use a sheet to store your "Parameters" and use for example A$2$ to store your value, to later use it in the formula.

If the users are not very experienced, they may break the vlookup formula too (specially if they do not understand what it is doing). In the parameters approach all you have to do is change your parameters in the parameters sheet and you are good to go. If you have many conditions even in the vlookup solution, using a parameters shhet is a better alternative. Since we are dealing with not advance users. Maybe you should include how to create a distinct list of your options, why do you use $$ in the range in the vlookup function, etc.

[#6]

The beauty of a forum like this is that there are always different approaches to achieve the same thing and that people can pick and chose what works best for their specific situation.

I used Vlookup because my interpretation is that eventually, all text values may need to be turned into a number. In that case, a nested if is too complex for my preference. Yes, it can be parameterized with values that are stored in another sheet, but then, the other sheet might as well be used for the lookup table and a much shorter formula with Vlookup. It's not too hard to understand Vlookup and many people will find that easier than nested IFs.