IIf and value-errors
 

Not sure why your original formula doesn't work (not sure why you're using
FIND) but this should do what you want.

=IF(LEFT(B3;2)="07";"M";IF(LEFT(B3;1)="0";"F";"U") )

--
Ian
--
"david" wrote in message
news:1ha8fml.11oh8s8ndnm8lN%messages.from.usenetRE ...
I have a column with numbers A (as text, I want to keep initial zeros):

05...
07...
08...
12...
41...

In another column B I want a letter that depends on the initial numbers
in the A-column. If the initial number is 0 I want an "F" in B, if it is
07 I want "M" in B and in all other cases I want an "U" in B:

F 05...
M 07...
F 08...
U 12...
U 41...

I created a formula for this purpose:

=IF(
FIND("07";LEFT(B3;2);1);
"M";
IF(FIND("0";LEFT(B3;1);1);"F";"U"))

The problem is that it only works for the M-cases. All other cases
return a value-error. However, if I break out line 4 in the formula it
works as expected. I am puzzled.

Any ideas why it doesn't work and how I can fix it?

This is in MacExcel but that shouldn't matter, should it?

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

IIf and value-errors
 

Hi David,
You're puzzled? What about me?
My Excel rejected the use of ; where there should have been ,
I came up with the same formula as Ian, but with commas, not
semicolons.
What's going on there?
Using FIND didn't work because if the text is not found the #VALUE
error is returned rather than FALSE and this stuffs up the whole
formula.
Ian's formula works because it traps the 07's first so they're then not
included in the testing for the 0's
Ken Johnson

IIf and value-errors
 

The list separator is defined by a windows setting--under regional settings.
Most people in the USA use a comma for the list separator. Many in other parts
of the world use a semicolon.

And if you did want to use =find() or =search() (not a particularly good fit in
this example), you could check to see if it actually found something:

=if(isnumber(find("ken",a1)),"Yep","nope")

=find() is case sensitive
=search() is not.

Ken Johnson wrote:

Hi David,
You're puzzled? What about me?
My Excel rejected the use of ; where there should have been ,
I came up with the same formula as Ian, but with commas, not
semicolons.
What's going on there?
Using FIND didn't work because if the text is not found the #VALUE
error is returned rather than FALSE and this stuffs up the whole
formula.
Ian's formula works because it traps the 07's first so they're then not
included in the testing for the 0's
Ken Johnson

--

Dave Peterson

IIf and value-errors
 

Thanks for that David.
Ken Johnson

IIf and value-errors
 

Thanks Dave
Ken Johnson

IIf and value-errors
 

Hi David,
Can you guarantee that there are no leading spaces. If not then perhaps
you should include the TRIM function as well...

=IF(LEFT(TRIM(B3);2)="07";"M";IF(LEFT(TRIM(B3);1)= "0";"F";"U"))

Ken Johnson

IIf and value-errors
 

Hi David,
Or, is the formula addressing the correct row in column B?
Ken Johnson

IIf and value-errors
 

I was responding more to Ken's followup.

But I would have thought that Ian's formula would have been sufficient.

Are you sure those 07's are really text--not numbers with a custom format (like
00)?

david wrote:

Dave Peterson wrote:

And if you did want to use =find() or =search() (not a particularly good
fit in this example), you could check to see if it actually found
something:

=if(isnumber(find("ken",a1)),"Yep","nope")

=find() is case sensitive
=search() is not.

I used find because I thought I had too.

Any other suggestions? Isn't it weird that the first two 07... returns
different results?

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

--

Dave Peterson

IIf and value-errors
 

Looking at the results you have shown, I tend to agree with Ken Johnson's
suggestion that you may be referencing the wrong row. The quoted formula
referencing B3 should be in row 3. It looks like it may be in row 2,
therefore referencing the row below itself (hence the first M is from the
second 07, U from the 41, F from the 01, M from the 07 and I suspect the
next entry below the ones quoted begins 07).

--
Ian
--
"david" wrote in message
news:1ha8kq2.8lsiu290o3sN%messages.from.usenetREMO ...
Ian wrote:

Not sure why your original formula doesn't work (not sure why you're
using
FIND) but this should do what you want.

=IF(LEFT(B3;2)="07";"M";IF(LEFT(B3;1)="0";"F";"U") )

This one behaved very unpredictable. The firs few lines from my sheet,
erroneous results are marked with an * with the correct letter in the
next column:

M 07...
U 07... * M
F 41... * U
M 01... * F
M 07...


This didn't look to good;-) Any further suggestions?


--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

IIf and value-errors
 

Hi david,
That clears up that mystery.
Thanks for letting us know.
Ken Johnson