Forum Discussion
Text to numeric
Hi , I am a total excel novice , have been trying to create a timesheet formula where by I could enter text in 1 box and have it allocate a pre set value in another , eg "early " would give a return value of 5 , "mid " a value of 6 and " late " a value of 8 and so forth.
Any help would be most appreciated .
Thank you
3 Replies
- Hello,
Let say you have:
Early in cell A1
Mid in cell A2
Late in cell A3
Or
List Data Validation drop-down in cells A1:A10 (for example)
In output cell, execute the Nested IF Statement below: =IF(A1="early",5,IF(A1="mid",6,8))
Then, copy down the formula An alternative solution is to create a table and use VLOOKUP to retrieve the value. See attached for an example of how that would work. The advantage of tables is that they can give you greater flexibility if , say, you wanted to change the values associated with one or more of the words.
You could do a nested IF statement or what I propose which is SWITCH as:
=SWITCH(TRUE,B2="early",5,B2="mid",6,B2="late",8,"so forth")
