VALUE err calculating indirect number

[expect_ed]

I copy an paste a web page into sheet 1.

Sheet 2, cell A10 has the formula: ="Sheet1!A"&(RIGHT(A9,2)+3)
Because the values I need to work with end up in every third row after
pasting(actually the web page results in Excel merging 3 cells together).

Sheet 2, cell F10 has the formula: =INDIRECT(A10) and results in 09
appearing as the result in the cell.

Now I want to do calculations based on that value of 9, but no matter what I
do I get a #VALUE! Error. I've tried setting the cell format of each of the
relevant cells to number, I've tried using the VALUE function to convert F10
to a number, which also gives a VALUE! error. I'm stuck.

Any ideas appreciated.
ed

 

[shg]

Have you tried replacing the formula in F10 with

=VALUE(INDIRECT(A10))

 

[David Biddulph]

The fact that you are seeing 09 rather than 9 suggests that you've got text,
rather than a number, in the relevant column. That is by no means unusual
when copying from the web. Go to the cell with the source data and look out
for spaces and other non-printing characters. Non-breaking spaces,
CHAR(160), are a frequent cause of such problems.

 

[Chip Pearson]

I can't replicate your error, but instead of

=F10
coerce it to a number with something like

=1*F10
or
=0+F10
or
=N(F10)

This shouldn't be necessary but it can't hurt.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[expect_ed]

shg - yes, =VALUE(INDIRECT(A10)) also gives the VALUE! error.

David - I think you are onto the problem, but how do I get rid of
non-breaking spaces formulaicly. I tried reading the web copy directly with:

=VALUE(TRIM(Sheet1!A61)) and it still gives me the VALUE! error.

 

[David Biddulph]

Ifyou look at Excel Help for the TRIM function, you will see that it says
(inter alia):
"Important The TRIM function was designed to trim the 7-bit ASCII space
character (value 32) from text. In the Unicode character set, there is an
additional space character called the nonbreaking space character that has a
decimal value of 160. This character is commonly used in Web pages as the
HTML entity,  . By itself, the TRIM function does not remove this
nonbreaking space character. For an example of how to trim both space
characters from text, see Remove spaces and non-printing characters from
text."

I would suggest that you follow the latter link in Help.

 

[shg]

=substitute(a1, char(160), ""

 

[expect_ed]

That did it - Thanks