Problem with nesting an OR in a formula
 

Hi

I have a formula which is starting to get beyond my knowledge, I don't know
where to put the OR!

=IF(LEFT(C10,1)="F","AC"&MID(C10,2,7),"AC"&MID(C10 ,1,7))

My problem is I would like to test if the 1st character is an F or 0 then
drop it, but I still need an 8 digit account number. I can see from the
help it is possible but whereever I put it I just get an error in the
formula

Can anyone help?

Thanks

Wendy

Problem with nesting an OR in a formula
 

Not sure exactly what you are after. You can check if the first
character is an F or 0 and drop it with:

=IF(or(LEFT(C10,1)="F",LEFT(C10,1)="0"),"AC"&MID(C 10,2,7),"AC"&MID(C10,1
,7))

However, this only gives you an account number that starts AC and then
has up to 7 digits. Did you want 8 digits with 0's at the beginning (eg
so 1234567 becomes 01234567)? If so, try:

="AC"&TEXT(IF(OR(LEFT(C10,1)="F",LEFT(C10,1)="0"), MID(C10,2,7),MID(C10,1
,7)),"00000000")

Andy

*** Sent via Developersdex http://www.developersdex.com ***

Problem with nesting an OR in a formula
 

Wendy, solve for a true or false as your first step and "build out" as you
develop your formula; that is, whether the first character for the cell in
question is either an F or a 0, solve that first:

=OR(Left(C10,1)="F",Left(C10,1)=0) ------- this will return a TRUE or a
FALSE

then, build out with the formula by adding your IF in front of the OR...

If(OR(Left(C10,1)="F",Left(C10,1)=0),"AC"&MID(C10, 2,7),"AC"$MID(C10,1,7))

If the "OR" portion of the formula returns a "true," then it will execute
the first bit of code ("AC"&MID(C10,2,7)); if it is false, it will activate
the second piece of code ("AC"$MID(C10,1,7)).

Cheers! Brad



"Wendy" wrote in message
...
Hi

I have a formula which is starting to get beyond my knowledge, I don't
know
where to put the OR!

=IF(LEFT(C10,1)="F","AC"&MID(C10,2,7),"AC"&MID(C10 ,1,7))

My problem is I would like to test if the 1st character is an F or 0 then
drop it, but I still need an 8 digit account number. I can see from the
help it is possible but whereever I put it I just get an error in the
formula

Can anyone help?

Thanks

Wendy