write a formula in excel to return a number for a nun numeric data cel

[jschill2628]

Is there a way to write a formula in excel to return a number for a cell with nun numeric data in it. For example, if I have two columns and in my first column I have some cells filled with data and some without (the data is non numeric). In the next column I want to put either a one if there is data in the cell or a zero when there is no data in the cell, for further analysis.

Or would this be an easy task for Crystal to do? If so, please help me understand what the formula would be.

Thanks in advance.

 

[fisheromacse]

I am not sure i understand the request entirely, but....
you could create a formula to evaluate the record and if it is null display a 0 otherwise display a 1.

//{@datahere}
IF isnull({table.field}) then 0 else 1

if you are looking for numbers only...

//{@numberhere}
IF isnull({table.field}) then 0 else
(IF isnumeric({table.field}) then 1 else 0)

 

[kristal9]

in Excel the formula would be this

=IF(ISBLANK(A4),0,IF(ISTEXT(A4),0,1))

assuming your value is in A4.

I'm not sure how a {table.field} in Crystal could be text or numeric. I always thought it had to be one or the other.

 

[IanWaterman]

When Crystal analyses the Excel column it assumes all data in column is same as first cell.

So if first cell is numeric and second cell string crystal does get a bit upset.

If vice versa all is OK as it converts numbers to numeric strings.

Ian

 

[SkipVought]

This is a typical problem with spreadsheets. Numeric values mixed in a text column cannot be 'fixed' using a FORMAT. FORMAT changes NOTHING. Numbers must be converted to a string of digits. This can be accomplished by prefixing an APOSTROPHY to the number as...
[tt]
'0123
[/tt]
You could also use the Data > Text to columns feature and specify TEXT. This will also perform a comversion.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}