If formula with text strings!

[almst791]

Can someone make me a formula to display the following. I have a column of numbers in row a so a1=7500300 and I want column be to have a function to tell that it is FA - Indirect. Is there any if statement that can handle the following requirements?

75XX3xxx = FA - Indirect
75XX7xxx = FA - R&D
75XX4xxx = FA - Sales & Marketing
75XX5xxx = FA - Sales & Marketing
75XX6xxx = FA - Admin
7XXX = FA - DGP

 

[Gerald Higgins]

Presumably the "x" characters represent variable characters ?

Such that 75003000 and 75123456 should both be treated as "FA - Indirect" ?

 

[steve the fish]

Heres a horrid looking formula:

=IF(AND(LEFT(E1)="7",LEN(E1)=4),"FA - DGP",IF(AND(LEFT(E1,2)="75",LEN(E1)=8),IF(MID(E1,5,1)="3","FA - Indirect",IF(OR(MID(E1,5,1)="4",MID(E1,5,1)="5"),"FA - Sales & Marketing",IF(MID(E1,5,1)="6","FA - Admin",IF(MID(E1,5,1)="7","FA R&D","")))),""))

 

[Gerald Higgins]

Also, do any of these codes start with numbers OTHER THAN 75 ?

If not, then the 75 part is irrelevant, and we only need to worry about
a) Character 5
b) Codes with 4 digits versus 8 digits

 

[Marcílio_Lobão]

almst791, good afternoon.

Perhaps a small table can help you in this research.
https://www.sendspace.com/file/zfw0r7

You will use the VLOOKUP function instead of the IF function which is not very efficient in this case.(my opinion)

Is that what you want?
I hope it helps.

 

[almst791]

Presumably the "x" characters represent variable characters ?

Such that 75003000 and 75123456 should both be treated as "FA - Indirect" ?

Yes X's are variable number!

 

[almst791]

Also, do any of these codes start with numbers OTHER THAN 75 ?

If not, then the 75 part is irrelevant, and we only need to worry about
a) Character 5
b) Codes with 4 digits versus 8 digits

Still confused on how to do this

 

[Gerald Higgins]

Well you could start by answering the question

Here it is again.
"Do any of these codes start with numbers OTHER THAN 75 ?"

Also, you could try the solution suggested by steve the fish. I haven't tested it myself, have you ?

 

[almst791]

Well you could start by answering the question

Here it is again.
"Do any of these codes start with numbers OTHER THAN 75 ?"

Also, you could try the solution suggested by steve the fish. I haven't tested it myself, have you ?

Sorry, all numbers begin with 75, but steve's formula looked like it would work but when I put it into my worksheet it always registers a false value.

 

[Gerald Higgins]

Consider Marcilio's suggestion of a VLOOKUP table.

=if(len(a1)=4,"FA - DGP",vlookup(mid(a1,5,1,false)+0,$b$1:$c$5,2,false))

where A1 contains your code
where b1:c5 contains your look up table, like this

Col B...Col C
3.........FA - Indirect
7.........FA - R & D

and so on.

Watch out for mismatch between the values in Col B, and your source data.
My formula assumes they are both numbers, not text, but you might need to try the text option instead.