format Varchar2 field as number . . unless it contains a letter.

[jkupov]

I'm having a strange sort of day and my mind is jumbled. I haven't posted here in years but always got the help I needed when I have posted. I'm hoping for some help again. I'm sure on any other day the solution would come to me but it's just one of those.

I have a field which is stored as varchar. Most of the values are numeric except for about 3%. There's good reason for that and I don't think it is something that will change. However, I need to export this into excel as a a number EXCEPT for the 3% which are alphanumeric. I need those to remain alphanumeric. I don't think the VAL function will work because that will just return the numeric portion of the field and not retain the letter.

If I use if numerictext then tonumber it will show a zero for the field...

*sigh*

Any ideas? I should just sleep on it but I need it done sooner than later and don't have the time to put it aside for too long.

Regards,

jk

 

[kray4660]

Not sure if the isnumeric would help. It should only be true if the field just returns a number not alphanumeric.

 

[jkupov]

Thanks kray4660, I tried that but it;s looking for a numeric value for the else clause. I want it to either return the numeric value if it is only numeric and the alphanumeric if it is alphanumeric.

 

[pmax9999]

I think you would need to do this from within excel after export.

In Crystal, a formula result can only be a single data type, ie all text or all numeric. You therefore can't have a formula where the result is either text or numeric based on some condition (such as containing alpha characters).


Cheers
Pete

 

[jkupov]

OK Thanks Pete-that seems to make sense of what I'm seeing. Excel it is!