Need a Macro to use part of the contents of one cell to fill in another

Hi everyone! I'm pretty new to editing macros in excel, and everything I've done so far was a modification to a code I found online somewhere. I've hit a brick wall now though because I can't find anything remotely like what I need out there that I can build off of. Long story short, my office has a computer system that generates reports with a code made up mostly of letters that refers to each of our 27 offices. So if the code starts with "MPA", I know it refers to HR regardless of how many extra characters are after the "A". It's always the 3rd or 4th character of the code that is the identifier, but the letter code could be any length. Traditionally, to fill in the name of offices the codes refer to, I've used a saved spreadsheet and a two different VLOOKUP function with embedded LEFT's inside, but that takes a while and is hard to explain to the non-excel users in the office for their own use. Yesterday I sat down to try to come up with an IF function that would do it all for us. However, I didn't realize that there was a limit of only 7 nested IF's for one function and I need 26 nested IF's, so that didn't work. So now I'm trying to make a macro, but I don't know enough to know how to pull it off. Here's a piece of the 26 long nested if I made yesterday.

=IF( LEFT($A2, 3)="MPA", "HR", IF( LEFT($A2,3)= "MPL", "Buis Ops", IF( LEFT($A2,4)= "MPRN", "Payroll", IF( LEFT($A2,4)= "MPRQ", "Marketing”, “none"))

So I need to find a way to turn that IF into a much longer macro. While I know I can figure out how to do the IF and ELIF's relatively easily with some online research, it's the beginning of the VB code I'm not sure about and how to pull off the LEFT code in VB. I'm not even sure how to reference the column I need it to look at vs. the one I want it to fill in.

Any ideas or suggestions?

Thanks for your help!

You can just concatenate the if statements rather than nesting them. That should overcome the 7 limit.

Formula:

Please Login or Register  to view this content.