# Thread: Formula Query (Excel 2003)

1. ## Formula Query (Excel 2003)

I have set up the following formula to identify the 1st 2 values of a cell (which is in the format [2 characters (numeric)][3 characters (alpha/numeric)] and then I compare the numeric characters against 2 values to classify the codes.

=IF(ISBLANK(E808),"",IF(LEFT(E807,2)<Groups!\$E\$2," DNG","DAM"))

The problem I have is the result LEFT(E807,2) returned a text value and I am comparing this against a numeric value so I cannot get the final value correctly.

Can anyone advise how I can convert this result to a numeric value so the comparison against the 2 cells will return the correct classification I am looking for.

2. ## Re: Formula Query (Excel 2003)

Try N(LEFT(E807,2))
The N function converts any value to a number, if possible.

3. ## Re: Formula Query (Excel 2003)

Thanks Hans

I have tried this but no change.

When I use this formula by itself looking at a cell which contains 91LR1 it returns no value. If I delete the n() part of the formula I get the 91 as the value.

4. ## Re: Formula Query (Excel 2003)

Try the following trick then: replace LEFT(E807,2) by

(0+LEFT(E807,2))

5. ## Re: Formula Query (Excel 2003)

Hans, Thankyou that worked.

Your effort and assistance is really appreciated.

6. ## Re: Formula Query (Excel 2003)

I can't find a N() function in XL2K. It does seem to exist since I can us it in a formula, but if I put '35 in A1 and =N(A1) in another cell, it returns 0. I can't find N() in the help files. Did you maybe mean =VALUE()?

7. ## Re: Formula Query (Excel 2003)

=N() has been around a while, see if it's under Information Functions. =VALUE() will atttempt to convert a text string to a number, where N will not convert text strings, returning 0 instead:

in =N(value)
If value is or refers to a number =N(value) returns that number
If value is or refers to a date in a built-in date format =N(value) returns the serial number of that date
If value is or refers to TRUE =N(value) returns 1
If value is or refers to FALSE =N(value) returns 0
If value is or refers to an error value, such as #DIV/0!, =N(value) returns the error value
If value is or refers to anything else =N(value) returns 0

8. ## Re: Formula Query (Excel 2003)

If that is what N() does, then it is definitely not the function that Hans meant to use in this response. VALUE() does what he wanted to do.

9. ## Re: Formula Query (Excel 2003)

You are correct, Legare, I should have used VALUE instead of N. Thanks to you and unkamunka for pointing it out and doing the research.

10. ## Re: Formula Query (Excel 2003)

That unkumunka, he's a great guy, isn't he? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

11. ## Re: Formula Query (Excel 2003)

Both the N() and T() functions are pretty much redundant as far as I know, but are included for compatibility with older spreadsheet programs.

They would most likely have a use if we did not have ISTEXT() and ISNUMBER().

Andrew

12. ## Re: Formula Query (Excel 2003)

Oops, I'm very sorry, John. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Thanks!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•