Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting Problem | Excel Discussion (Misc queries) | |||
HLP! Nesting Problem?? | Excel Worksheet Functions | |||
Nesting Problem | Excel Worksheet Functions | |||
Nesting problem? | Excel Programming | |||
nesting problem | Excel Programming |