I am trying to create a simple part number configurator using an Excel
spreadsheet. (I am using Excel 2007)

* Cell B3 is where the user enters the base part number (i.e. the code
assigned to the base part without any add-on components). That will
always have to be entered manually by the user.
* Cells D2 - AC2 have codes that specify all the add-on components that
could be assembled with the base part.
* Cells D3 - AC3 is where the user inserts an "X" and selects which
add-on component (options) will be added to the base part.
* Cell AD3 would be a concatenated string composed by the base part
number (cell B3), a dash (-), and the codes of any of the add-ons
shown in cells D2-AC2 and selected with an "X" in cells D3-AC3, each
one separated by a forward slash (/). This would be the complete
part number of the assembled product.

For instance:
* Cell B3 shows "C6520" as the base part number. (This will always have
to be inserted manually.)
* Cells D2-H2 have codes such as "AG", "AI", "AR", "BE", "BL". (Note:
I am only showing the first five options instead of the full 26!)
* Cells D3, F3, and H3 have an "X" in their cell.
* The part number string in cell AD3 would be: C6520-AG/AR/BL

I really would prefer an Excel formula rather than a macro...

I think this formula will do what you want (copy it down as needed)...

=SUBSTITUTE(SUBSTITUTE(TRIM(B3&"-"&D3&" "&E3&" "&F3&" "&G3&" "&H3&" "&I3&"
"&J3&" "&K3&" "&L3&" "&M3&" "&N3&" "&O3&" "&P3&" "&Q3&" "&R3&" "&S3&"
"&T3&" "&U3&" "&V3&" "&W3&" "&X3&" "&Y3&" "&Z3&" "&AA3&" "&AB3&" "&AC3),"
","/"),"-/","-")

Unfortunately, those lines got word wrapped at blank spaces. To make it
easier for you to copy and not miss any of the blanks, here is the formula
again, this time manually word wrapped to avoid the problem...

=SUBSTITUTE(SUBSTITUTE(TRIM(B3&"-"&D3&" "&E3&" "&F3&" "&G3&" "&
H3&" "&I3&" "&J3&" "&K3&" "&L3&" "&M3&" "&N3&" "&O3&" "&P3&" "&
Q3&" "&R3&" "&S3&" "&T3&" "&U3&" "&V3&" "&W3&" "&X3&" "&Y3&" "&
Z3&" "&AA3&" "&AB3&" "&AC3)," ","/"),"-/","-")

Rick Rothstein (MVP - Excel)