Excel Formula =Value WHy it doesn't work for text?

Question

I am creating a worksheet that is taking info from another tab however when the info I am copying is numerical, it works but when its letters it doesnt. i.e. Cell A1 contains 12345 and Cell A2 contains Chicken. In another random cell i put in the formula =Value(A1) and it gives me 12345. However when i do the same for cell A2 I get the error #VALUE! Ive noticed the value formula works for numbers but not tet. What formula do i need to use?
Thanks

Answer 1

As AnalProgrammer mentions, you don't need the VALUE() function at all. Just use the formula =A1 or =A2. If you need the values in another worksheet, you can pull values from Sheet1:

='Sheet1'!A1

='Sheet1'!A2



The exclamation point is required syntax; the single quotes/apostrophes merely help if the sheet name has spaces (e.g. '2010 Data'!F25).



The VALUE() function does a very specific task: if the cell contains a text entry composed of digits (which to Excel is very different from the value of the digits themselves) it will convert it to a numerical value.



If for example you set B1 as text format and enter 123—it has a text value of "123"—and SUM() it with B2 which has the numeric value 234, the result will be 234, since "123" is text. However, SUM(VALUE(B1), B2) would return 357.



Since "Chicken" cannot be converted to a decimal number, it makes VALUE() throw an error.

Answer 2

You can do this without reformatting your information. The trick is to do it in two separate steps. Here's how: In column D, put this formulation in D2: =SEARCH( "avenue", B2). Replica this method down as far as your rows contain data. You're going to see some cells show a number while others return #value! Error. Do not worry about it, just cover column D. Now put this system in cellphone the place you need the whole to appear: =SUMIF(D:D, ">0", C:C) that's it, enjoy your components! King Regards, QwertyKPH @ Yahoo PS: I simply seen your title is Gidget. I knew a lady in Wichita high school (East) about 25+ years in the past with that title. Very exotic title. Couldn't be you would it?

Answer 3

Just use A1 or A2. No formula is needed.



Have fun.