If formula with text strings!

almst791

Board Regular
Joined
Jun 29, 2016
Messages
82
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Presumably the "x" characters represent variable characters ?

Such that 75003000 and 75123456 should both be treated as "FA - Indirect" ?
 
Upvote 0
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","")))),""))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top