Home 
Search 
Today's Posts 
#1




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 addon components). That will always have to be entered manually by the user. * Cells D2  AC2 have codes that specify all the addon components that could be assembled with the base part. * Cells D3  AC3 is where the user inserts an "X" and selects which addon 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 addons shown in cells D2AC2 and selected with an "X" in cells D3AC3, 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 D2H2 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: C6520AG/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) 
#2




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 addon components). That will always have to be entered manually by the user. * Cells D2  AC2 have codes that specify all the addon components that could be assembled with the base part. * Cells D3  AC3 is where the user inserts an "X" and selects which addon 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 addons shown in cells D2AC2 and selected with an "X" in cells D3AC3, 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 D2H2 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: C6520AG/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) 
#3




Need Help With String Concatenation
On Sep 16, 8:50*am, "Rick Rothstein"
wrote: 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 addon components). *That will * *always have to be entered manually by the user. * *Cells D2  AC2 have codes that specify all the addon components that * *could be assembled with the base part. * *Cells D3  AC3 is where the user inserts an "X" and selects which * *addon 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 addons * *shown in cells D2AC2 and selected with an "X" in cells D3AC3, 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 D2H2 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: *C6520AG/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) Hide quoted text   Show quoted text  Ingenious Rick, but it doesn't grab the code names from row 2. 
#4




Need Help With String Concatenation
Ingenious Rick, but it doesn't grab the code names from row 2.
Not so ingenious then, was it. <g This formula, considerably longer, should work... =SUBSTITUTE(SUBSTITUTE(TRIM(B3&""&LEFT(D$2,9*(D3="X"))&" "&LEFT(E$2,9* (E3="X"))&" "&LEFT(F$2,9*(F3="X"))&" "&LEFT(G$2,9*(G3="X"))&" "&LEFT( H$2,9*(H3="X"))&" "&LEFT(I$2,9*(I3="X"))&" "&LEFT(J$2,9*(J3="X"))&" "& LEFT(K$2,9*(K3="X"))&" "&LEFT(L$2,9*(L3="X"))&" "&LEFT(M$2,9*(M3="X"))& " "&LEFT(N$2,9*(N3="X"))&" "&LEFT(O$2,9*(O3="X"))&" "&LEFT(P$2,9*(P3="X"))& " "&LEFT(Q$2,9*(Q3="X"))&" "&LEFT(R$2,9*(R3="X"))&" "&LEFT(S$2,9*(S3="X"))& " "&LEFT(T$2,9*(T3="X"))&" "&LEFT(U$2,9*(U3="X"))&" "&LEFT(V$2,9*(V3="X"))& " "&LEFT(W$2,9*(W3="X"))&" "&LEFT(X$2,9*(X3="X"))&" "&LEFT(Y$2,9*(Y3="X"))& " "&LEFT(Z$2,9*(Z3="X"))&" "&LEFT(AA$2,9*(AA3="X"))&" "&LEFT(AB$2,9*( AB3="X"))&" "&LEFT(AC$2,9*(AC3="X")))," ","/"),"/","") Rick Rothstein (MVP  Excel) 
#5




Need Help With String Concatenation
Actually, this formula would be better as it hides the dash when the formula
is copied down through rows that do not have data filled in them yet... =SUBSTITUTE(SUBSTITUTE(TRIM(B6&LEFT(""&D$2,9*(D6="X"))&" "&LEFT(E$2, 9*E6="X"))&" "&LEFT(F$2,9*(F6="X"))&" "&LEFT(G$2,9*(G6="X"))&" "&LEFT( H$2,9*(H6="X"))&" "&LEFT(I$2,9*(I6="X"))&" "&LEFT(J$2,9*(J6="X"))&" "& LEFT(K$2,9*(K6="X"))&" "&LEFT(L$2,9*(L6="X"))&" "&LEFT(M$2,9*(M6="X"))& " "&LEFT(N$2,9*(N6="X"))&" "&LEFT(O$2,9*(O6="X"))&" "&LEFT(P$2,9*(P6="X"))& " "&LEFT(Q$2,9*(Q6="X"))&" "&LEFT(R$2,9*(R6="X"))&" "&LEFT(S$2,9*(S6="X"))& " "&LEFT(T$2,9*(T6="X"))&" "&LEFT(U$2,9*(U6="X"))&" "&LEFT(V$2,9*(V6="X"))& " "&LEFT(W$2,9*(W6="X"))&" "&LEFT(X$2,9*(X6="X"))&" "&LEFT(Y$2,9*(Y6="X"))& " "&LEFT(Z$2,9*(Z6="X"))&" "&LEFT(AA$2,9*(AA6="X"))&" "&LEFT(AB$2,9*( AB6="X"))&" "&LEFT(AC$2,9*(AC6="X")))," ","/"),"/","") Rick Rothstein (MVP  Excel) 
#6




Need Help With String Concatenation
On Sep 16, 9:35*am, "Rick Rothstein"
wrote: Actually, this formula would be better as it hides the dash when the formula is copied down through rows that do not have data filled in them yet... =SUBSTITUTE(SUBSTITUTE(TRIM(B6&LEFT(""&D$2,9*(D6="X"))&" "&LEFT(E$2, 9*E6="X"))&" "&LEFT(F$2,9*(F6="X"))&" "&LEFT(G$2,9*(G6="X"))&" "&LEFT( H$2,9*(H6="X"))&" "&LEFT(I$2,9*(I6="X"))&" "&LEFT(J$2,9*(J6="X"))&" "& LEFT(K$2,9*(K6="X"))&" "&LEFT(L$2,9*(L6="X"))&" "&LEFT(M$2,9*(M6="X"))& " "&LEFT(N$2,9*(N6="X"))&" "&LEFT(O$2,9*(O6="X"))&" "&LEFT(P$2,9*(P6="X"))& " "&LEFT(Q$2,9*(Q6="X"))&" "&LEFT(R$2,9*(R6="X"))&" "&LEFT(S$2,9*(S6="X"))& " "&LEFT(T$2,9*(T6="X"))&" "&LEFT(U$2,9*(U6="X"))&" "&LEFT(V$2,9*(V6="X"))& " "&LEFT(W$2,9*(W6="X"))&" "&LEFT(X$2,9*(X6="X"))&" "&LEFT(Y$2,9*(Y6="X"))& " "&LEFT(Z$2,9*(Z6="X"))&" "&LEFT(AA$2,9*(AA6="X"))&" "&LEFT(AB$2,9*( AB6="X"))&" "&LEFT(AC$2,9*(AC6="X")))," ","/"),"/","") Rick Rothstein (MVP  Excel) Good one. And super quick! 
#7




Need Help With String Concatenation
=SUBSTITUTE(SUBSTITUTE(TRIM(B6&LEFT(""&D$2,9*(D6="X"))&" "&LEFT(E$2,
9*E6="X"))&" "&LEFT(F$2,9*(F6="X"))&" "&LEFT(G$2,9*(G6="X"))&" "&LEFT( H$2,9*(H6="X"))&" "&LEFT(I$2,9*(I6="X"))&" "&LEFT(J$2,9*(J6="X"))&" "& LEFT(K$2,9*(K6="X"))&" "&LEFT(L$2,9*(L6="X"))&" "&LEFT(M$2,9*(M6="X"))& " "&LEFT(N$2,9*(N6="X"))&" "&LEFT(O$2,9*(O6="X"))&" "&LEFT(P$2,9*(P6="X"))& " "&LEFT(Q$2,9*(Q6="X"))&" "&LEFT(R$2,9*(R6="X"))&" "&LEFT(S$2,9*(S6="X"))& " "&LEFT(T$2,9*(T6="X"))&" "&LEFT(U$2,9*(U6="X"))&" "&LEFT(V$2,9*(V6="X"))& " "&LEFT(W$2,9*(W6="X"))&" "&LEFT(X$2,9*(X6="X"))&" "&LEFT(Y$2,9*(Y6="X"))& " "&LEFT(Z$2,9*(Z6="X"))&" "&LEFT(AA$2,9*(AA6="X"))&" "&LEFT(AB$2,9*( AB6="X"))&" "&LEFT(AC$2,9*(AC6="X")))," ","/"),"/","") Good one. And super quick! Thanks, but you know I did not type all of that out, right? I ran a loop and let it output the repetitive part and then just slapped the SUBSTITUTE parts on the front and back it. Rick 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Need Help With String Concatenation  Excel Worksheet Functions  
Concatenation  Excel Discussion (Misc queries)  
Concatenation  Excel Worksheet Functions  
Bold in string concatenation  Excel Discussion (Misc queries)  
How do I put each member of string concatenation in its own line?  Excel Worksheet Functions 