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

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

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

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

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

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

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