Numbers formatted as inches (") won't calculate

**ozizushi** · 04-07-2011, 12:01 PM

A portion of my spreadsheet takes dimensions in inches and calculates area. I believe that it worked at one time but doesn't anymore. The dimensions are formatted to place a " after the number. I have tried the following custom number formats:
##''
#0.000[$'']
0.00\"

They all do a satisfactory job of displaying the numbers but they are not working in my formulas.

For example: cell A1 displays 21". In cell A2 I write the formula =A1*2. Cell A2 displays #VALUE!

How do I get Excel to recognize these as numbers?

**NBVC** · 04-07-2011, 12:06 PM

Are you sure that you are not physically typing in the " after the 21 in the cell as well.

If you look at the formula bar you should not see the " at the end of the number.

**ozizushi** · 04-07-2011, 12:10 PM

Yes, I'm sure. The formatting places the " after the number. I do not add the quotes.

**ozizushi** · 04-07-2011, 12:13 PM

Oops. You were right. I had referenced another cell that contained a quote. Thanks a bunch!

**NBVC** · 04-07-2011, 12:14 PM

The only way I can think of for getting that error is in A1 is actually a text string...

Even if you enter 21 as text, if there is nothing else the multiplication will convert it... so there must be another character in A1 along with 21...