Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text vs Numbers
I have a bunch of numbers like 123-5-30 and bunch of text in a column. I
want to use a "if" formula that gives me a "Yes" for number and a "No" in adjacent column. Can you help? I tried it but it sees the numbers as text and provides a No for both situation. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text vs Numbers
123-5-30 is text, not a number
If you want to test for whether it would have been a number if you hadn't put the hyphens in, use the test =ISNUMBER(--(SUBSTITUTE(A1,"-",""))) so perhaps =IF(ISNUMBER(--(SUBSTITUTE(A1,"-",""))),"Yes","No") -- David Biddulph "Howard" wrote in message ... I have a bunch of numbers like 123-5-30 and bunch of text in a column. I want to use a "if" formula that gives me a "Yes" for number and a "No" in adjacent column. Can you help? I tried it but it sees the numbers as text and provides a No for both situation. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text vs Numbers
I like David's answer but here is another
=IF(ISNUMBER(--LEFT(A1,2)),"Yes","No") will test if the first two charters in tour text an digits - you can alter the 2 as needed best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Howard" wrote in message ... I have a bunch of numbers like 123-5-30 and bunch of text in a column. I want to use a "if" formula that gives me a "Yes" for number and a "No" in adjacent column. Can you help? I tried it but it sees the numbers as text and provides a No for both situation. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using TEXT and &TEXT - display numbers with commas, underline text | Excel Discussion (Misc queries) | |||
how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |