Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Posts
    88
    Thanks
    3
    Thanked 1 Time in 1 Post

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

    Thanks.
    --
    tb

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Something like:
    =LEFT(B3&"-"&IF(D3="X",D2&"/","")&IF(E3="X",E2&"/","")&IF(F3="X",F2&"/","")&IF(G3="X",G2&"/","")&IF(H3="X",H2&"/",""),LEN(B3&"-"&IF(D3="X",D2&"/","")&IF(E3="X",E2&"/","")&IF(F3="X",F2&"/","")&IF(G3="X",G2&"/","")&IF(H3="X",H2&"/",""))-1)

    You will have to add the other 21 cells combinations.

    An option with intermediate calcs to avoid the long entry is to place in D4 the formula:
    =C4&IF(D3="X",D2&"/","")

    Then copy this from D4 to H4 (or beyond) and then use the formula:
    =B3&"-"&LEFT(H4,LEN(H4)-1)

    [change to the 2 x H4 to the final cell in the range...]

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •