Here is a version that uses the search function (non-case-sensitive version of find). The set-up is as follows.
In sheet 1, the codes to be looked up begin in column A of Sheet 1. The final result will be in column B. Columns C, D ,etc .have the unique codes arranged horizontally in row 1, i.e., "BK" in C1, "R" in D2, etc. The practical way to achieve this is to simply copy the code list in the lookup table and Paste Special Transpose them horizontally in cells C1, D1, etc.
Then first in cell B2 enter the following formula:
Copy this formula across the rows from column C to how many code columns you Ceated in row 2.
Finally, in cell C2, concatenate all the results for row 2, i.e., the formula
=D2&" "&E2&" "&F2
etc., for all the columns with codes in row 1. This step is tedious, but can be shorted with the following VBA function, which allows all the cells in a range to be concatenated:
Function Concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return the result as a single string
' useful when you have a large range of cells that you need to concatenate
' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Dim retVal As String, dlm As String, cell As Range
retVal = ""
If delim = Null Then
dlm = ""
dlm = delim
For Each cell In useThis
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
retVal = retVal & CStr(cell.Value) & dlm
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
Concat = retVal
You would insert and copy this function into a module in Developer VBA. Usage is simple--concat(C1:D1," "), for example.
Note that this approach works for all 2-character codes, and all 1-character codes if they are not in the 2+ character codes, that is, if there are no code pairs such as "R" and "BR".