Safe way to check what is in a cell

[Ragnar Midtskogen]

Hello,

I am writing an application that is supposed to parse a spreadsheet file
into individual files dependent on what is in the first column.
Each block of rows with the same numeric string or number is used to create
a new file, i.e. a change iin number signals end of a file.
Lines with anything else are to be ignored.

For this reason, I need to check the first column for one of the following:
1. A string of numerals, f.ex. "014"
2. A number, f.ex. 610
3. #N/A
4. Blank
5. Anything else

So, I need a safe way to tell if I have either 1 or 2, or one of the rest.

I have code that handles 1, 2 and 3 right now, but then I tried another
sample and found blanks instead of #N/A.

I am thinking now that I should first check for #N/A, then retrieve the
contents into a Variant variable and check for blank.
If it is neither, check variable if it is numeric.
If it is I have my numeric string, otherwise it is a 5.

Are there any possible surprises in this?

Any help would be appreciated.

Ragnar

 

[Dave Smith]

If I understand correctly, the values shown in column A should be sorted out
as shown in column B.

A B
1 '014 Numeric String
2 610 Number
3 #N/A Other
4 Other
5 ljk Other

I believe that this formula will do it:
=IF(ISNUMBER(A1), "Number",
IF(OR(ISERROR(VALUE(A1)),ISBLANK(A1)),"Other","Numeric String"))

HTH.

-Dave Smith