| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Determine Value of Cell?
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Determine Value of Cell?
hi
=if(ISNUMBER(B1),B1*1,"") regards FSt1 " wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Determine Value of Cell?
In C1:
=if(iserror(B1*1),"",B1*1) and copy down -- Gary''s Student - gsnu2007c " wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Determine Value of Cell?
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 "FSt1" wrote in message ... hi =if(ISNUMBER(B1),B1*1,"") regards FSt1 " wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Determine Value of Cell?
hi.
i tested before posting. in 2003, my formula returned numbers when numbers and blanks when letters. your formula works also. regards FSt1 "David Biddulph" wrote: 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 "FSt1" wrote in message ... hi =if(ISNUMBER(B1),B1*1,"") regards FSt1 " wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Determine Value of Cell?
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 "FSt1" wrote in message ... hi. i tested before posting. in 2003, my formula returned numbers when numbers and blanks when letters. your formula works also. regards FSt1 "David Biddulph" wrote: 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 "FSt1" wrote in message ... hi =if(ISNUMBER(B1),B1*1,"") regards FSt1 " wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Determine Value of Cell?
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 "David Biddulph" wrote: 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 "FSt1" wrote in message ... hi. i tested before posting. in 2003, my formula returned numbers when numbers and blanks when letters. your formula works also. regards FSt1 "David Biddulph" wrote: 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 "FSt1" wrote in message ... hi =if(ISNUMBER(B1),B1*1,"") regards FSt1 " wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to determine color of a cell? | Excel Worksheet Functions | |||
| How can I determine cell color? | Excel Discussion (Misc queries) | |||
| How can I determine if a cell has a value (#) or a formula (X*Y)? | Excel Discussion (Misc queries) | |||
| in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell? | Excel Worksheet Functions | |||
| Function to determine if any cell in a range is contained in a given cell | Excel Worksheet Functions | |||
Linear Mode
