ExcelBanter - Assigning a number value to text

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Assigning a number value to text (https://www.excelbanter.com/excel-worksheet-functions/135910-assigning-number-value-text.html)

Assigning a number value to text

Does anyone know how to get Excel to recognize a word as a numeric value? I
have gone into InsertNameDefine and assigned a numeric value to the word.
Lets say the word is "Manager" and the value is "28.00". The job title and
hourly wage. If I type "Manager' into a formula it will count it as 28.00,
which is good, but if I refer to a cell that has "Manager' in it, it does not
recognize it as a number. Thanks for the help.

Answer: Assigning a number value to text

Yes, I can definitely help you with that!

To get Excel to recognize a word as a numeric value, you can use the VLOOKUP function. Here are the steps:

Create a table with two columns: one for the words you want to assign a numeric value to, and another for the numeric values themselves. In your case, you would have "Manager" in one column and "28.00" in the other.
Select the entire table, including the headers.
Go to the Formulas tab and click on "Define Name" in the Defined Names group.
In the New Name dialog box, give your table a name (e.g. "JobTitles") and make sure the "Refers to" field shows the range of your table.
Click OK to close the dialog box.
Now you can use the VLOOKUP function to assign numeric values to your words. For example, if you have a list of job titles in column A and want to assign their numeric values in column B, you can use the following formula in cell B2:

Formula:

=VLOOKUP(A2,JobTitles,2,FALSE)

This formula looks up the value in cell A2 (e.g. "Manager") in the table named "JobTitles" and returns the corresponding value in the second column (e.g. "28.00").
Copy the formula down to the rest of the cells in column B.

That's it! Now Excel will recognize the words in column A as numeric values based on the table you defined.

Assigning a number value to text

A1: "manager" (no quotes, no =) ... with value of 28

B1: =EVAL(A1)*10 will give result of 280

HTH

"SoupNazi" wrote:

Does anyone know how to get Excel to recognize a word as a numeric value? I
have gone into InsertNameDefine and assigned a numeric value to the word.
Lets say the word is "Manager" and the value is "28.00". The job title and
hourly wage. If I type "Manager' into a formula it will count it as 28.00,
which is good, but if I refer to a cell that has "Manager' in it, it does not
recognize it as a number. Thanks for the help.

Getting Excel to recognise given text as a value

Soupnaz et al - I am trying to do the same as you I think. I am woefully poor with Excel (basic formulas only) not sure I even know what a macro is.... what I am trying to do is a gibe a name (say becky) a value, say $10.80 so that whenever the name is typed in excel knows to multiply the hours enetred next to it x becky.... any ideas gratefully received and rewarded if you coem to Bath UK.....

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Getting Excel to recognise given text as a value

In A1:B5 I have this list
Becky 10.50 (so a1 has Becky, B1 has 10.50)
Mary 12.45
Jane 10.75
Bob 12.00
Allan 20.45
I selected A1:B5 and used Insert | Name | Create

In C1 I have type Becky (caps do not matter)
In D1 I have type 2.5
In E1 I have the formula =INDIRECT(C1)*D1 and it displays 26.25
I could format it to show £26.25

In C2 I have typed Bob and in D2 I enters 3, I copied the formula from E1 to
E2 it shows 36
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Jon Overton wrote in message ...
Soupnaz et al - I am trying to do the same as you I think. I am woefully
poor with Excel (basic formulas only) not sure I even know what a macro
is.... what I am trying to do is a gibe a name (say becky) a value, say
$10.80 so that whenever the name is typed in excel knows to multiply the
hours enetred next to it x becky.... any ideas gratefully received and
rewarded if you coem to Bath UK.....

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

MS EXCEL- Assigning texts a value in a formula

I am trying to assign a value to a text which is wrapped in an IF() formula.

EG
A1= G1
B1= 250
C1= G1 (Can be a number e.g 400)
D1= 2005
E1= 200

My formula looks like:
=if(isnumber (c1), (if (d1<2000,0,(if d1=2003,(B1-E1),(B1-C1)),0)

This returns #VALUE

---
However, i am trying to get C1 to be seen as a "0" or have it assigned to zero if the cell contains a text.

How do i do this?

Any help appreciated.

Bernard Liengme wrote:

Getting Excel to recognise given text as a value
03-Dec-07

In A1:B5 I have this list
Becky 10.50 (so a1 has Becky, B1 has 10.50)
Mary 12.45
Jane 10.75
Bob 12.00
Allan 20.45
I selected A1:B5 and used Insert | Name | Create

In C1 I have type Becky (caps do not matter)
In D1 I have type 2.5
In E1 I have the formula =INDIRECT(C1)*D1 and it displays 26.25
I could format it to show ?26.25

In C2 I have typed Bob and in D2 I enters 3, I copied the formula from E1 to
E2 it shows 36
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Jon Overton wrote in message ...

Previous Posts In This Thread:

On Thursday, March 22, 2007 1:50 AM
SoupNaz wrote:

Assigning a number value to text
Does anyone know how to get Excel to recognize a word as a numeric value? I
have gone into InsertNameDefine and assigned a numeric value to the word.
Lets say the word is "Manager" and the value is "28.00". The job title and
hourly wage. If I type "Manager' into a formula it will count it as 28.00,
which is good, but if I refer to a cell that has "Manager' in it, it does not
recognize it as a number. Thanks for the help.

On Thursday, March 22, 2007 2:10 AM
Topper wrote:

Assigning a number value to text
A1: "manager" (no quotes, no =) ... with value of 28

B1: =EVAL(A1)*10 will give result of 280

HTH

"SoupNazi" wrote:

On Monday, December 03, 2007 5:08 PM
Jon Overton wrote:

Getting Excel to recognise given text as a value
Soupnaz et al - I am trying to do the same as you I think. I am woefully poor with Excel (basic formulas only) not sure I even know what a macro is.... what I am trying to do is a gibe a name (say becky) a value, say $10.80 so that whenever the name is typed in excel knows to multiply the hours enetred next to it x becky.... any ideas gratefully received and rewarded if you coem to Bath UK.....

On Monday, December 03, 2007 5:49 PM
Bernard Liengme wrote:

Getting Excel to recognise given text as a value
In A1:B5 I have this list
Becky 10.50 (so a1 has Becky, B1 has 10.50)
Mary 12.45
Jane 10.75
Bob 12.00
Allan 20.45
I selected A1:B5 and used Insert | Name | Create

In C1 I have type Becky (caps do not matter)
In D1 I have type 2.5
In E1 I have the formula =INDIRECT(C1)*D1 and it displays 26.25
I could format it to show ?26.25

In C2 I have typed Bob and in D2 I enters 3, I copied the formula from E1 to
E2 it shows 36
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Jon Overton wrote in message ...

Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 4.0 In a Nutshell [O'Reilly]
http://www.eggheadcafe.com/tutorials...c-40-in-a.aspx

MS EXCEL- Assigning texts a value in a formula

The formula you posted has parentheses out of order. Maybe that's your
problem. Try:
=if(isnumber(c1),if(d1<2000,0,if(d1=2003,B1-E1,B1-C1)),0)

If not, copy your formula and paste it to your message, so we know exactly
what formula you are using.

Regards,
Fred

"Keisha Carter" wrote in message
.. .
I am trying to assign a value to a text which is wrapped in an IF()
formula.

EG
A1= G1
B1= 250
C1= G1 (Can be a number e.g 400)
D1= 2005
E1= 200

My formula looks like:
=if(isnumber (c1), (if (d1<2000,0,(if d1=2003,(B1-E1),(B1-C1)),0)

This returns #VALUE

---
However, i am trying to get C1 to be seen as a "0" or have it assigned to
zero if the cell contains a text.

How do i do this?

Any help appreciated.

Bernard Liengme wrote:

Getting Excel to recognise given text as a value
03-Dec-07

In A1:B5 I have this list
Becky 10.50 (so a1 has Becky, B1 has 10.50)
Mary 12.45
Jane 10.75
Bob 12.00
Allan 20.45
I selected A1:B5 and used Insert | Name | Create

In C1 I have type Becky (caps do not matter)
In D1 I have type 2.5
In E1 I have the formula =INDIRECT(C1)*D1 and it displays 26.25
I could format it to show ?26.25

In C2 I have typed Bob and in D2 I enters 3, I copied the formula from E1
to
E2 it shows 36
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Jon Overton wrote in message ...

Previous Posts In This Thread:

On Thursday, March 22, 2007 1:50 AM
SoupNaz wrote:

Assigning a number value to text
Does anyone know how to get Excel to recognize a word as a numeric value?
I
have gone into InsertNameDefine and assigned a numeric value to the
word.
Lets say the word is "Manager" and the value is "28.00". The job title and
hourly wage. If I type "Manager' into a formula it will count it as 28.00,
which is good, but if I refer to a cell that has "Manager' in it, it does
not
recognize it as a number. Thanks for the help.

On Thursday, March 22, 2007 2:10 AM
Topper wrote:

Assigning a number value to text
A1: "manager" (no quotes, no =) ... with value of 28

B1: =EVAL(A1)*10 will give result of 280

HTH

"SoupNazi" wrote:

On Monday, December 03, 2007 5:08 PM
Jon Overton wrote:

Getting Excel to recognise given text as a value
Soupnaz et al - I am trying to do the same as you I think. I am woefully
poor with Excel (basic formulas only) not sure I even know what a macro
is.... what I am trying to do is a gibe a name (say becky) a value, say
$10.80 so that whenever the name is typed in excel knows to multiply the
hours enetred next to it x becky.... any ideas gratefully received and
rewarded if you coem to Bath UK.....

On Monday, December 03, 2007 5:49 PM
Bernard Liengme wrote:

Getting Excel to recognise given text as a value
In A1:B5 I have this list
Becky 10.50 (so a1 has Becky, B1 has 10.50)
Mary 12.45
Jane 10.75
Bob 12.00
Allan 20.45
I selected A1:B5 and used Insert | Name | Create

In C1 I have type Becky (caps do not matter)
In D1 I have type 2.5
In E1 I have the formula =INDIRECT(C1)*D1 and it displays 26.25
I could format it to show ?26.25

In C2 I have typed Bob and in D2 I enters 3, I copied the formula from E1
to
E2 it shows 36
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Jon Overton wrote in message ...

Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 4.0 In a Nutshell [O'Reilly]
http://www.eggheadcafe.com/tutorials...c-40-in-a.aspx