I have 600 item codes, and each one is coded with attributes in the overall item code. For example, we might have the plain item 600, then 600BK (Black body) and 600BKR(Black Body, Red colored detail) and 600BKR-YEL (Black body, red colored detail, Yellow base).

So I've got the list of item codes:


and then on a separate worksheet, an lookup list of each code and what it stands for:

BK    Black Body
R     Red Detail
YEL   Yellow Base 

I'd like to write a function that would find every code in the item title that is on the lookup sheet, and then return all of the corresponding values for each found code, preferably in one cell.

600BK         Black Body
600BKR        Black Body Red Detail
600BKR-YEL    Black Body Red Detail Yellow Base

Is this possible?


I would do something like the following. Assuming that your list of item codes is in column A of Sheet1 and, say, rows 2-20, and your lookup list is in columns A and B of Sheet2, rows 2-10:

  =IFERROR(VLOOKUP(MID(A2,4,2),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,6,1),Sheet2!$A$2:$B$4,2,0),"")&" "

in cell B2 and then copied down for all the rows in Sheet1.

While it does put the lookups in a single formula, this approach would be very cumbersome if you have many codes. I would recommend that you insert helper rows in Sheet1 for each code position (e.g. mid(A2,4,2), and then concatenate them into a single string.

  • This unfortunately won't work on all of the items, because the codes can be in all different lengths, and are occasionally in different orders. Also, the plain/parent items can be anywhere from 3-5 characters. That said, when it works, the output here is exactly what I wanted to see. If only there was a way to make it more dynamic. – Matt Jan 9 '13 at 18:53
  • I also created a variant that searched for the codes, which works perfectly if the codes are at least two characters--i.e., if there is not a "R" and another code "BR". Dealing with that case is more difficult because you would have to look on either side of the "R" to make sure it was not a "BR," or perhaps an "RK." Are you locked in to the single character codes? – chuff Jan 9 '13 at 19:03
  • For the set I am working with now, there is some character overlap but it's not significant as long as I look for the longest codes first (for example, S and SN). The second set of codes that I will hopefully be working on tomorrow does have some overlap, though. That set has B as a code, but also BB. It gets a little confusing with those items. – Matt Jan 9 '13 at 19:18

The short answer is "Yes", one way how is rather longer.

Since there are many possibilities not excluded in your question (such as plain items coded longer than three numbers or alphanumeric, more than three codes per plain item, codes varying in significance depending upon plain item etc) I’d suggest safest to start with parsing the codes. This should avoid complications with the likes of GR being either Grey Body with Red Detail (often a compatible colour scheme!) or Green Body.

Assuming these have been parsed (in three columns B:D starting in Row2) thereafter it is easy, with your lookup table (the one on the separate sheet) say named codes:


in your item codes sheet. To get spaces between the values I have assumed all the entries in the right hand column of your code array end in a space (easy enough to arrange such as with the likes of =A2&" " copied down, etc).

So the difficult part may be to parse the codes out of the item codes, for which I’d suggest adding columns as below (more if there are more than three codes):

SU531526 first example

with formulae as below:

SU531526 second example

ColumnB is to define where to start looking for codes (in case plain codes are other than three numeric). ColumnsC:D are for where to start searching for the next code/the length of the next code. I would agree not elegant, but relatively versatile. Make sure the parsing is correct on the left before working towards the right.

After successful parsing I suggest Copy/Paste Special/Values (to get rid of the formulae) then replace blank with a full stop (to avoid the lookup formula errorring out without making it more complex)*. Also, assuming throughout that your list of item codes is in ColumnA, delete ColumnsB:F before applying the lookup formula as above (or adjust the references to suit) -and add more lookups if necessary.

*Oversight: Make sure a full stop is added in a cell in each column of the lookup table.

Add columns for c5, c6 and c7 to allow for (a) maximum length 7 with (b) 'worst case' scenario (ie all single character).

  • This is pretty close to functional, but having to do columns C:F manually, along with having to mess around with the B column each time there's a new main item code (not to mention some of the items don't even come in a plain variation) makes it almost as time consuming as just hand typing each code. However, I'm wondering if there's a way to do the same work of those columns with the find command and something like SMALL(). Any ideas? – Matt Jan 9 '13 at 18:50
  • For the set I am working with today, the two most frequent codes are S and BK, but there are around a dozen others that pop up occassionally, and a handful that only show up once. Example codes could be 14440BK and 14440SBK, 8BKSN, and 484BK. The base codes can be anywhere from 1 to 5 digits, and on this set the base codes are almost all numeric (to the point where I can just go through and manually take care of ones that are not). The bulk of tomorrow's codes start with a single letter, and then have a 2-3 digit number, followed by a code that is anywhere from 1-7 alphabetical characters. – Matt Jan 9 '13 at 19:29

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
  End If
  For Each cell In useThis
      If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
          retVal = retVal & CStr(cell.Value) & dlm
      End If
  If dlm <> "" Then
     retVal = Left(retVal, Len(retVal) - Len(dlm))
  End If
  Concat = retVal
  End Function

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".

  • Yes, you are absolutely right about the C1, C2 references (consequence of rushing). I'll correct the answer. Those overlapping code pairs are a tough nut to crack. If we could count on a consistent ordering of the codes, it might be possible to deal with them. – chuff Jan 9 '13 at 20:30
  • I think this is pretty close to my best option, especially since I've realized I'm able to split the codes into three categories (Body, Base, Detail). Unfortunately, there's still the issue of code overlap in some instances. I'll chew it over tonight, and see what I can work out from this tomorrow. – Matt Jan 9 '13 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.