Colby

On your workbook generate a matrix which has the codes in one column, and the descriptions in the next. Name that range "codess". On thing, make sure that the codes are numeric. When I tested this, I had the lookup table in the same workbook as the data. You may have to modify the code to nominate the workbook that contains the lookup table.

I'm assuming that the data that comes in is in text form, so that if there is a code which starts with a 0, then the 0 will be visible. That way, each code is 3 characters long.

The code to parse each cell into a 3 char string, then look it up in the lookup table, and output it in the subsequent columns is:

For i = 1 To Len(ActiveCell) / 3
ActiveCell.Offset(0, i).Value = WorksheetFunction.VLookup(Val(Mid(ActiveCell, i * 3 - 2, 3)), Range("codess"), 2, False)
Next i

Note that there is a mix of text and value here. That is why I specified that the lookup table had to be numbers, and the input values have to be text. Text is required to ensure that the data is of the correct length. I've converted the text to the value to get the lookup to work easily.


Tony