# If formula with text strings!

#### almst791

##### Board Regular
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
7XXX = FA - DGP

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Gerald Higgins

##### Well-known Member
Presumably the "x" characters represent variable characters ?

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

#### steve the fish

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
almst791, good afternoon.

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

##### Board Regular
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

##### Board Regular
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-known Member
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

##### Board Regular
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

##### Well-known Member
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 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.

Replies
4
Views
415
Replies
3
Views
216
Replies
4
Views
2K
Replies
2
Views
2K
Replies
5
Views
303

1,191,165
Messages
5,985,033
Members
439,935
Latest member
Monty238

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back