I have a list of user ids in column A that have a department prefix. For example, HRSALLY (Human Resources Sally), SHJASON (Shipping Jason), ACGEORGE (Accounting George), etc. I want to be able to populate column B with the department managers name based on the department prefix.

The end result should be HRSALLY remains in Column A and column B has the HR Manager's name. Next row shows SHJASON in Column A and the Shipping Manager's name in column B, etc.



Suggestions on how to do?