View Single Post
  #1   Report Post  
Old September 15th 11, 09:45 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein Rick Rothstein is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default Need Help With String Concatenation

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),"
","/"),"-/","-")

Rick Rothstein (MVP - Excel)