How to use IF, MID, AND in same formula?

[Warbo]

Hi All,
I'm trying to write a formula(s) which split out some customer coding information so it can be cleanly moved - the idea is to combine all these into one formula so all information in one column (any suggestions on that too greatly appreciated)

So the rules are;

• IF the customer number 5th letter is an A - it is a 'Statement' level code
  Successful formula I used - IF(MID(CELL,5,1)="A","STATEMENT","")
  
  
• IF the customer number has 6 characters or more, it is a 'Delivery' level code
  Successful formula I used - IF(LEN(CELL)=>6,"DELIVERY","")
  
  
• IF the customer number 5th letter is anything but A - it is an 'Invoice' level code - but I don't want it to consider text strings longer than 5 characters (as they Delivery level codes)
  Unsuccessful attempts - I've got as far as suggesting - IF(MID(CELL,5,1)<>"A","INVOICE","") - and a few attempts at trying to stick LEN, LEFT, with an AND statement, but all failed.
  Below image to illustrate that Invoice currently showing incorrectly against AA01B01

Many thanks in advance!

Dan

 

[awoohaw]

try:

Mr excel questions 39.xlsm
	A	B	C	D
1	cust_no	Statement (5 Letters end "A")	Invoice (5 Letters Not "A")	Delivery Point 6 Characters or more
2	AA01A	STATEMENT	STATEMENT	STATEMENT
3	AA01B	DELIVERY	DELIVERY	DELIVERY
4	AA01B01	INVOICE	INVOICE	INVOICE
Sheet5

Cell Formulas
Range	Formula
B2:D4	B2	=IF(AND(LEN($A2)<=5,MID($A2,5,1)="A"),"STATEMENT", IF(AND(LEN($A2)>5,MID($A2,5,1)<>"A"),"INVOICE","DELIVERY"))

 

[awoohaw]

try:

Mr excel questions 39.xlsm
	A	B	C	D
1	cust_no	Statement (5 Letters end "A")	Invoice (5 Letters Not "A")	Delivery Point 6 Characters or more
2	AA01A	STATEMENT	STATEMENT	STATEMENT
3	AA01B	DELIVERY	DELIVERY	DELIVERY
4	AA01B01	INVOICE	INVOICE	INVOICE
Sheet5

Cell Formulas
Range	Formula
B2:D4	B2	=IF(AND(LEN($A2)<=5,MID($A2,5,1)="A"),"STATEMENT", IF(AND(LEN($A2)>5,MID($A2,5,1)<>"A"),"INVOICE","DELIVERY"))

This assumes anything else is delivery if that has other requirement you need to specify.

 

[awoohaw]

Actually, this may be a better formula for you:

Mr excel questions 39.xlsm
	A	B	C	D
1	cust_no	Statement (5 Letters end "A")	Invoice (5 Letters Not "A")	Delivery Point 6 Characters or more
2	AA01A	STATEMENT
3	AA01B		INVOICE
4	AA01B01			DELIVERY
Sheet5

Cell Formulas
Range	Formula
B2,D4,C3	B2	=IF(LEN($A2)>5,"DELIVERY", IF(AND(LEN($A2)<=5,MID($A2,5,1)="A"),"STATEMENT","INVOICE"))

 

[awoohaw]

Actually, this may be a better formula for you:

Mr excel questions 39.xlsm
	A	B	C	D
1	cust_no	Statement (5 Letters end "A")	Invoice (5 Letters Not "A")	Delivery Point 6 Characters or more
2	AA01A	STATEMENT
3	AA01B		INVOICE
4	AA01B01			DELIVERY
Sheet5

Cell Formulas
Range	Formula
B2,D4,C3	B2	=IF(LEN($A2)>5,"DELIVERY", IF(AND(LEN($A2)<=5,MID($A2,5,1)="A"),"STATEMENT","INVOICE"))

A more concise formula:

=IF(LEN($A2)>5,"DELIVERY",
IF(MID($A2,5,1)="A","STATEMENT","INVOICE"))

 

[Warbo]

A more concise formula:

=IF(LEN($A2)>5,"DELIVERY",
IF(MID($A2,5,1)="A","STATEMENT","INVOICE"))

Awoohaw - you sir, are a hero - that has worked an absolute treat - thank you! I was overcomplicating it all!

 

[awoohaw]

@Warbo I'm happy you found a solution, thanks for the feedback.

And welcome to the Mr. Excel forum.