| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Allow only nummeric values
Hi,
I have a requirement wherein I need to format the sheet to accept only numerical values. Any other data (numerical or special characters) should be changed to "N/A". is this possible in Excel ? Thx SPA |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Allow only nummeric values
I mean any other data <Alphabetic or special characters..
"sapai" wrote: Hi, I have a requirement wherein I need to format the sheet to accept only numerical values. Any other data (numerical or special characters) should be changed to "N/A". is this possible in Excel ? Thx SPA |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Allow only nummeric values
one way
use <data <validation allow decimal greater than enter a value something like -1,000,000,000 -- Greetings from New Zealand "sapai" wrote in message ... I mean any other data <Alphabetic or special characters.. "sapai" wrote: Hi, I have a requirement wherein I need to format the sheet to accept only numerical values. Any other data (numerical or special characters) should be changed to "N/A". is this possible in Excel ? Thx SPA |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Allow only nummeric values
Thanks Bill,
I also have another requirement wherein invalid data is to be replaced by "N/A". is this possible having already avoided alpha? "Bill Kuunders" wrote: one way use <data <validation allow decimal greater than enter a value something like -1,000,000,000 -- Greetings from New Zealand "sapai" wrote in message ... I mean any other data <Alphabetic or special characters.. "sapai" wrote: Hi, I have a requirement wherein I need to format the sheet to accept only numerical values. Any other data (numerical or special characters) should be changed to "N/A". is this possible in Excel ? Thx SPA |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Allow only nummeric values
Describe "numerical" for us. Obviously all digits meet that criteria. What
about numbers with a decimal point in them? Or a leading plus and/or minus sign. How about with thousand's separators. Excel recognizes 1.23E+45 as number... is this acceptable to you too? What about numbers with leading currency symbols or trailing % symbols? Or any other variations that I might be forgetting at the moment? -- Rick (MVP - Excel) "sapai" wrote in message ... Hi, I have a requirement wherein I need to format the sheet to accept only numerical values. Any other data (numerical or special characters) should be changed to "N/A". is this possible in Excel ? Thx SPA |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Allow only nummeric values
You are right Rick.
I was too generic in my statement. What i am actually looking at is percentage values. user may enter from 1 to 100. I am not looking at any decimal values. - SPA "Rick Rothstein" wrote: Describe "numerical" for us. Obviously all digits meet that criteria. What about numbers with a decimal point in them? Or a leading plus and/or minus sign. How about with thousand's separators. Excel recognizes 1.23E+45 as number... is this acceptable to you too? What about numbers with leading currency symbols or trailing % symbols? Or any other variations that I might be forgetting at the moment? -- Rick (MVP - Excel) "sapai" wrote in message ... Hi, I have a requirement wherein I need to format the sheet to accept only numerical values. Any other data (numerical or special characters) should be changed to "N/A". is this possible in Excel ? Thx SPA |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Allow only nummeric values
I'm sorry, but I have a couple of follow up questions for you. What do you
want to happen if a floating point value is entered between 1 and 100 (such as 12.3)? Do you want it rounded to the nearest integer or flagged "N/A"? Do you want to preserve the errant value typed in, but just display it as "N/A" (in other words, show "N/A" in the cell, but keep the value in the Formula Bar) or is it okay to physically replace the errant value with "N/A" (thus losing it completely)? Is a VB solution permissible? How close we can come to what you want will depend on how you answer all of the questions above (please don't skip any). The VB solution will allow us to come the closest to whatever you end up specifying... a non-VB solution will require some compromises. -- Rick (MVP - Excel) "sapai" wrote in message ... You are right Rick. I was too generic in my statement. What i am actually looking at is percentage values. user may enter from 1 to 100. I am not looking at any decimal values. - SPA "Rick Rothstein" wrote: Describe "numerical" for us. Obviously all digits meet that criteria. What about numbers with a decimal point in them? Or a leading plus and/or minus sign. How about with thousand's separators. Excel recognizes 1.23E+45 as number... is this acceptable to you too? What about numbers with leading currency symbols or trailing % symbols? Or any other variations that I might be forgetting at the moment? -- Rick (MVP - Excel) "sapai" wrote in message ... Hi, I have a requirement wherein I need to format the sheet to accept only numerical values. Any other data (numerical or special characters) should be changed to "N/A". is this possible in Excel ? Thx SPA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
| Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
| add nummeric and alfa to one alfanumeric number | Excel Discussion (Misc queries) | |||
| Count unique values and create list based on these values | Excel Worksheet Functions | |||
| create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
Linear Mode
