How can I give text (A,B...) a number value in Excel (A=1, B=2..)
 

I'm trying to give a word/text a numeric value. High = 3, Medium = 2 and Low
= 1 etc.

I thought this could be done with a lookup table? If anyone has any
information on how to do this I'd be most appreciative.

Cheers,

Pete

Answer: How can I give text (A,B...) a number value in Excel (A=1, B=2..)
 

Hi Pete,

Yes, you can definitely use a lookup table to assign numeric values to text in Excel. Here's how you can do it:

1. Create a new table with two columns. In the first column, list all the text values you want to assign numeric values to (e.g. High, Medium, Low). In the second column, list the corresponding numeric values (e.g. 3, 2, 1).
2. Select the cell where you want to enter the formula that will convert the text to numeric values.
3. Type the following formula:
   Formula:

   ---

   =VLOOKUP(A1,Table1,2,FALSE) 


   ---

4. In this formula, "A1" is the cell containing the text value you want to convert, "Table1" is the name of the table you created in step 1, and "2" is the column number containing the numeric values.
5. Press Enter to apply the formula to the selected cell.
6. Now, whenever you enter a text value in the cell you selected, the formula will automatically convert it to the corresponding numeric value from your lookup table.

How can I give text (A,B...) a number value in Excel (A=1, B=2..)
 

=VLOOKUP(text,A:B,2,0)

Where text is your word (or cell address of text)

columns A & B contain your word to number table

A B
HIGH 3
MEDIUM 2
LOW 1

etc

HTH

"Pete" wrote:

I'm trying to give a word/text a numeric value. High = 3, Medium = 2 and Low
= 1 etc.

I thought this could be done with a lookup table? If anyone has any
information on how to do this I'd be most appreciative.

Cheers,

Pete

How can I give text (A,B...) a number value in Excel (A=1, B=2..)
 

Typically, you'd use MATCH, VLOOKUP, or LOOKUP to solve your issue....but, if
the cell may NOT always contain high, medium, or low...

This is durable against that situation and returns zero:
=SUM(COUNTIF(A1,{"Low","Medium","High"})*{1,2,3})

and it's shorter than something like this (which does the same thing):
=IF(ISNA(MATCH(A10,{"Low","Medium","High"},0)),0,M ATCH(A10,{"Low","Medium","High"},0))

Note: you could also list "Low","Medium","High" in a range and reference
that instead.

Does that give you something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Pete" wrote:

I'm trying to give a word/text a numeric value. High = 3, Medium = 2 and Low
= 1 etc.

I thought this could be done with a lookup table? If anyone has any
information on how to do this I'd be most appreciative.

Cheers,

Pete

How can I give text (A,B...) a number value in Excel (A=1, B=2
 

Hi Pete,

You can use VLOOKUP and still deal with blank cells. Create the lookup
table and set it up as follows:

A B
0 0
High 3
Low 2
Mid 1

Assume that the first 0 is in cell A1, To be pretty, name the range A1:B4 T.
Assume your first value to lookup is in E1, then your formula becomes:

=VLOOKUP(E1,T,2)

If E1 is blank this formula returns 0. Note that the table is sorted
Ascending on the first column.

Of course you don't need to use a range name:

=VLOOKUP(E1,A$1:B$4,2)

--
Cheers,
Shane Devenshire


"Ron Coderre" wrote:

Typically, you'd use MATCH, VLOOKUP, or LOOKUP to solve your issue....but, if
the cell may NOT always contain high, medium, or low...

This is durable against that situation and returns zero:
=SUM(COUNTIF(A1,{"Low","Medium","High"})*{1,2,3})

and it's shorter than something like this (which does the same thing):
=IF(ISNA(MATCH(A10,{"Low","Medium","High"},0)),0,M ATCH(A10,{"Low","Medium","High"},0))

Note: you could also list "Low","Medium","High" in a range and reference
that instead.

Does that give you something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Pete" wrote:

I'm trying to give a word/text a numeric value. High = 3, Medium = 2 and Low
= 1 etc.

I thought this could be done with a lookup table? If anyone has any
information on how to do this I'd be most appreciative.

Cheers,

Pete