I do almost all of my coding in VBA, not Excel, so this one is new to
me...

I have an external lookup plug-in (BBG) that returns "#N/A" if the
lookup fails. I have populated a column in my sheet with these
formulas, some of which return #N/A, and others that return numbers. I
would like to make a formula that uses the number if it's there, and
ignores it if it's #N/A.

In code I would simply refer do this:

Left(Range(A1).text,1) <> "#"

But how do I do this in an XL formula?

Maury

There is logical function ISNA so something like

=if(ISNA(A1), "Error", "OK")
--
HTH...

Jim Thomlinson


"Maury Markowitz" wrote:

> I do almost all of my coding in VBA, not Excel, so this one is new to
> me...
>
> I have an external lookup plug-in (BBG) that returns "#N/A" if the
> lookup fails. I have populated a column in my sheet with these
> formulas, some of which return #N/A, and others that return numbers. I
> would like to make a formula that uses the number if it's there, and
> ignores it if it's #N/A.
>
> In code I would simply refer do this:
>
> Left(Range(A1).text,1) <> "#"
>
> But how do I do this in an XL formula?
>
> Maury
>

=IF(ISNA(A1),"",A1)

"Maury Markowitz" wrote:

> I do almost all of my coding in VBA, not Excel, so this one is new to
> me...
>
> I have an external lookup plug-in (BBG) that returns "#N/A" if the
> lookup fails. I have populated a column in my sheet with these
> formulas, some of which return #N/A, and others that return numbers. I
> would like to make a formula that uses the number if it's there, and
> ignores it if it's #N/A.
>
> In code I would simply refer do this:
>
> Left(Range(A1).text,1) <> "#"
>
> But how do I do this in an XL formula?
>
> Maury
>

Thanks!