Determine Value of Cell?

[it]

Here's what I got:
A1=1007543
A2=10C49
B1=formula:mid(A1,3,2) [which displays 07]
B2=formula:mid(A1,3,2) [which displays C4]
C1=formula: B1*1 [which displays 7]
C2=formula: B2*1 [which displays #VALUE]

I'm trying to write a function for C that will display [blank] where
there is a 'character' in B and a [number] where there is a 'number'
in B.

I've tried:
isnumber - they aren't numbers
value - still get #value for C2

Any other ideas?

-Bruce

 

[FSt1]

hi
=if(ISNUMBER(B1),B1*1,"")

regards
FSt1

 

[Gary''s Student]

In C1:

=if(iserror(B1*1),"",B1*1) and copy down

 

[David Biddulph]

If you try it I think you'll find that will always return a blank, as the
MID formula in B retuns text, not a number.

Try =IF(ISNUMBER(--B1),--B1,"")
--
David Biddulph

hi
=if(ISNUMBER(B1),B1*1,"")

regards
FSt1

Here's what I got:
A1=1007543
A2=10C49
B1=formula:mid(A1,3,2) [which displays 07]
B2=formula:mid(A1,3,2) [which displays C4]
C1=formula: B1*1 [which displays 7]
C2=formula: B2*1 [which displays #VALUE]

I'm trying to write a function for C that will display [blank] where
there is a 'character' in B and a [number] where there is a 'number'
in B.

I've tried:
isnumber - they aren't numbers
value - still get #value for C2

Any other ideas?

-Bruce

 

[FSt1]

hi.
i tested before posting. in 2003, my formula returned numbers when numbers
and blanks when letters.
your formula works also.

regards
FSt1

If you try it I think you'll find that will always return a blank, as the
MID formula in B retuns text, not a number.

Try =IF(ISNUMBER(--B1),--B1,"")
--
David Biddulph

hi
=if(ISNUMBER(B1),B1*1,"")

regards
FSt1

Here's what I got:
A1=1007543
A2=10C49
B1=formula:mid(A1,3,2) [which displays 07]
B2=formula:mid(A1,3,2) [which displays C4]
C1=formula: B1*1 [which displays 7]
C2=formula: B2*1 [which displays #VALUE]

I'm trying to write a function for C that will display [blank] where
there is a 'character' in B and a [number] where there is a 'number'
in B.

I've tried:
isnumber - they aren't numbers
value - still get #value for C2

Any other ideas?

-Bruce

 

[David Biddulph]

Fascinating! I too am using Excel 2003 (SP2 in my case), and with the OP's
first line, 1007543 in A1, the MID formula in B1 returns 07 as a text
string, and =IF(ISNUMBER(B1),B1*1,"") returns a blank (as ISNUMBER(B1)
returns FALSE). I wonder what different settings you have from what I'm
using, and I wonder whether other users share your result? I'm intrigued
that you need a B1*1 when B1 returns true for ISNUMBER, but I ought by now
to have learned never to be surprised at anything from Excel.
--
David Biddulph

hi.
i tested before posting. in 2003, my formula returned numbers when numbers
and blanks when letters.
your formula works also.

regards
FSt1

If you try it I think you'll find that will always return a blank, as the
MID formula in B retuns text, not a number.

Try =IF(ISNUMBER(--B1),--B1,"")
--
David Biddulph

hi
=if(ISNUMBER(B1),B1*1,"")

regards
FSt1

:

Here's what I got:
A1=1007543
A2=10C49
B1=formula:mid(A1,3,2) [which displays 07]
B2=formula:mid(A1,3,2) [which displays C4]
C1=formula: B1*1 [which displays 7]
C2=formula: B2*1 [which displays #VALUE]

I'm trying to write a function for C that will display [blank] where
there is a 'character' in B and a [number] where there is a 'number'
in B.

I've tried:
isnumber - they aren't numbers
value - still get #value for C2

Any other ideas?

-Bruce

 

[FSt1]

hi
sorry it took so long to get back. having trouble signing into newsgroups.
anyway, my logic was that if it returns a number then multipy else don't.
but i do see your point now. i tested on a number 7 and that is why my
formula tested correct. after further testing, my formula fails as you
perdicted and for the reason you stated.
ya caught me!
thanks for the corrections. i'm be more careful in the future.

Regards
FSt1

Fascinating! I too am using Excel 2003 (SP2 in my case), and with the OP's
first line, 1007543 in A1, the MID formula in B1 returns 07 as a text
string, and =IF(ISNUMBER(B1),B1*1,"") returns a blank (as ISNUMBER(B1)
returns FALSE). I wonder what different settings you have from what I'm
using, and I wonder whether other users share your result? I'm intrigued
that you need a B1*1 when B1 returns true for ISNUMBER, but I ought by now
to have learned never to be surprised at anything from Excel.
--
David Biddulph

hi.
i tested before posting. in 2003, my formula returned numbers when numbers
and blanks when letters.
your formula works also.

regards
FSt1

If you try it I think you'll find that will always return a blank, as the
MID formula in B retuns text, not a number.

Try =IF(ISNUMBER(--B1),--B1,"")
--
David Biddulph

hi
=if(ISNUMBER(B1),B1*1,"")

regards
FSt1

:

Here's what I got:
A1=1007543
A2=10C49
B1=formula:mid(A1,3,2) [which displays 07]
B2=formula:mid(A1,3,2) [which displays C4]
C1=formula: B1*1 [which displays 7]
C2=formula: B2*1 [which displays #VALUE]

I'm trying to write a function for C that will display [blank] where
there is a 'character' in B and a [number] where there is a 'number'
in B.

I've tried:
isnumber - they aren't numbers
value - still get #value for C2

Any other ideas?

-Bruce