| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding cells with numbers
Hi all,
Does anyone know if there's a way to structure an if statement like: IF(cell A1 contains a number),show cell A1,leave default FALSE I've tried wildcards and I can't figure it out. I want to be able to make this work if any number 0-9 is the first character in the cell, so it would catch things like: 1 1939 20/20 1024-bit but not things like: Route 90 T1000 (I'm a T2 fan, can you tell? :) ) Thanks a lot! jezzica85 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding cells with numbers
=IF(ISNUMBER(A1),A1,FALSE)
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "jezzica85" wrote in message ... Hi all, Does anyone know if there's a way to structure an if statement like: IF(cell A1 contains a number),show cell A1,leave default FALSE I've tried wildcards and I can't figure it out. I want to be able to make this work if any number 0-9 is the first character in the cell, so it would catch things like: 1 1939 20/20 1024-bit but not things like: Route 90 T1000 (I'm a T2 fan, can you tell? :) ) Thanks a lot! jezzica85 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding cells with numbers
Bob Phillips wrote
=IF(ISNUMBER(A1),A1,FALSE) OP said: I want to be able to make this work if any number 0-9 is the first character in the cell, -- David |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding cells with numbers
I've been playing with this prob and the following formula works although it
is rather long! =IF(OR(LEFT(A1,1)="1",LEFT(A1,1)="2",LEFT(A1,1)="3 ",LEFT(A1,4)="1",LEFT(A1,5)="1",LEFT(A1,6)="1",LEF T(A1,7)="1",LEFT(A1,8)="1",LEFT(A1,9)="1",LEFT(A1, 1)="0"),A1,FALSE) Someone probably has an easier solution though so wait around! Judith -- Hope this helps "David" wrote: Bob Phillips wrote =IF(ISNUMBER(A1),A1,FALSE) OP said: I want to be able to make this work if any number 0-9 is the first character in the cell, -- David |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding cells with numbers
You could try =IF(OR(LEFT(A1,1)={"0","1","2","3","4","5","6","7" ,"8","9"}),"yes",FALSE) and leave the ,False off if you just wish it to default to False. -- JudithJubilee Wrote: I've been playing with this prob and the following formula works although it is rather long! =IF(OR(LEFT(A1,1)="1",LEFT(A1,1)="2",LEFT(A1,1)="3 ",LEFT(A1,4)="1",LEFT(A1,5)="1",LEFT(A1,6)="1",LEF T(A1,7)="1",LEFT(A1,8)="1",LEFT(A1,9)="1",LEFT(A1, 1)="0"),A1,FALSE) Someone probably has an easier solution though so wait around! Judith -- Hope this helps "David" wrote: Bob Phillips wrote =IF(ISNUMBER(A1),A1,FALSE) OP said: I want to be able to make this work if any number 0-9 is the first character in the cell, -- David -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=535423 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding cells with numbers
Thank you Bryan, this works great! And to everyone else, thanks for your
solutions too, I really appreciate it. Jezzica85 "Bryan Hessey" wrote: You could try =IF(OR(LEFT(A1,1)={"0","1","2","3","4","5","6","7" ,"8","9"}),"yes",FALSE) and leave the ,False off if you just wish it to default to False. -- JudithJubilee Wrote: I've been playing with this prob and the following formula works although it is rather long! =IF(OR(LEFT(A1,1)="1",LEFT(A1,1)="2",LEFT(A1,1)="3 ",LEFT(A1,4)="1",LEFT(A1,5)="1",LEFT(A1,6)="1",LEF T(A1,7)="1",LEFT(A1,8)="1",LEFT(A1,9)="1",LEFT(A1, 1)="0"),A1,FALSE) Someone probably has an easier solution though so wait around! Judith -- Hope this helps "David" wrote: Bob Phillips wrote =IF(ISNUMBER(A1),A1,FALSE) OP said: I want to be able to make this work if any number 0-9 is the first character in the cell, -- David -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=535423 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding cells with numbers
Judith,
You got your test value mixed up with the character position after 3 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "JudithJubilee" wrote in message ... I've been playing with this prob and the following formula works although it is rather long! =IF(OR(LEFT(A1,1)="1",LEFT(A1,1)="2",LEFT(A1,1)="3 ",LEFT(A1,4)="1",LEFT(A1,5 )="1",LEFT(A1,6)="1",LEFT(A1,7)="1",LEFT(A1,8)="1" ,LEFT(A1,9)="1",LEFT(A1,1) ="0"),A1,FALSE) Someone probably has an easier solution though so wait around! Judith -- Hope this helps "David" wrote: Bob Phillips wrote =IF(ISNUMBER(A1),A1,FALSE) OP said: I want to be able to make this work if any number 0-9 is the first character in the cell, -- David |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding cells with numbers
=ISNUMBER(--LEFT(A1,1))
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "David" wrote in message ... Bob Phillips wrote =IF(ISNUMBER(A1),A1,FALSE) OP said: I want to be able to make this work if any number 0-9 is the first character in the cell, -- David |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding cells with numbers
Hello,
=ABS(CODE(LEFT(A1,1))-52.5)<5 or =IF(ABS(CODE(LEFT(A1,1))-52.5)<5,"yes","no") HTH, Bernd |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I use "VLOOKUP" with cells containing both Text & Numbers? | Excel Worksheet Functions | |||
| Pivot Table keep apart cells text that I've formatted as numbers | Excel Worksheet Functions | |||
| Hightlighting Numbers & then all Cells to the right of these Numbers. | Excel Worksheet Functions | |||
| check if 2 cells are equal but only if they contain numbers not i. | Excel Worksheet Functions | |||
| Converting negative numbers in a range of cells to zero | Excel Discussion (Misc queries) | |||
Hybrid Mode
