Check numeric char
in [Programming]
|
Prev: String Search
Next: Save BeforeClose
From: Kash on 21 May 2010 01:29 Can anyone help with a formula/macro to check if a cell contains all numeric characters only? in which only "," can be allowed and not even a space.
From: Jacob Skaria on 21 May 2010 01:35 Try the below....returns TRUE if a number... =ISNUMBER(--SUBSTITUTE(A1,",",)) -- Jacob (MVP - Excel) "Kash" wrote: > Can anyone help with a formula/macro to check if a cell contains all numeric > characters only? in which only "," can be allowed and not even a space.
From: ozgrid.com on 21 May 2010 02:11 If the cells actually contain a comma they will be NON numeric no matter how numbers are in it. -- Regards Dave Hawley www.ozgrid.com "Kash" <Kash(a)discussions.microsoft.com> wrote in message news:54C5803F-E4C8-4FB4-9F4E-E5EC7F8F56B3(a)microsoft.com... > Can anyone help with a formula/macro to check if a cell contains all > numeric > characters only? in which only "," can be allowed and not even a space.
From: Helmut Meukel on 21 May 2010 03:54 I guess the OP lives in a country that uses the comma as decimal sign. Because ISNUMERIC is localized it will happyly accept a comma in such a locale but will return false if there is a point! Helmut. "ozgrid.com" <dave(a)ozgrid.com> schrieb im Newsbeitrag news:uLff%23xK%23KHA.3628(a)TK2MSFTNGP04.phx.gbl... > If the cells actually contain a comma they will be NON numeric no matter how > numbers are in it. > > -- > Regards > Dave Hawley > www.ozgrid.com > > > "Kash" <Kash(a)discussions.microsoft.com> wrote in message > news:54C5803F-E4C8-4FB4-9F4E-E5EC7F8F56B3(a)microsoft.com... >> Can anyone help with a formula/macro to check if a cell contains all numeric >> characters only? in which only "," can be allowed and not even a space. >
From: Helmut Meukel on 21 May 2010 04:33
"Kash" <Kash(a)discussions.microsoft.com> schrieb im Newsbeitrag news:54C5803F-E4C8-4FB4-9F4E-E5EC7F8F56B3(a)microsoft.com... > Can anyone help with a formula/macro to check if a cell contains all numeric > characters only? in which only "," can be allowed and not even a space. Hmm, I assume your localized version of Excel is using the comma as decimal sign, right? Then how about negative values? BTW, the suggested IsNumeric will accept values like "543E12" or "120D4" as numeric if used in VBA. The Excel spreadsheet function with the same name however will return False in the second case (tested with Excel 97). Excel will recognize it as a number if I enter 123E4 into a cell and automatically convert it to 1.23E+06 (=1230000) using scientific notation. 123D4 however is treated as text and not recognized as scientific notation. Helmut. |