Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 15th 11, 09:45 PM posted to microsoft.public.excel.worksheet.functions
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)


  #2   Report Post  
Old September 15th 11, 09:50 PM posted to microsoft.public.excel.worksheet.functions
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),"
","/"),"-/","-")


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   Report Post  
Old September 15th 11, 10:07 PM posted to microsoft.public.excel.worksheet.functions
Xt Xt is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 49
Default 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 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)- Hide quoted text -

- Show quoted text -


Ingenious Rick, but it doesn't grab the code names from row 2.
  #4   Report Post  
Old September 15th 11, 10:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default 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   Report Post  
Old September 15th 11, 10:35 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default 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   Report Post  
Old September 15th 11, 10:37 PM posted to microsoft.public.excel.worksheet.functions
Xt Xt is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 49
Default 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   Report Post  
Old September 15th 11, 10:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help With String Concatenation Xt Excel Worksheet Functions 1 September 15th 11 09:47 PM
Concatenation Muthalaly Excel Discussion (Misc queries) 2 June 24th 08 12:57 PM
Concatenation markmcd Excel Worksheet Functions 3 December 11th 07 12:29 AM
Bold in string concatenation Steen Excel Discussion (Misc queries) 12 November 7th 06 12:49 AM
How do I put each member of string concatenation in its own line? DViolette Excel Worksheet Functions 3 July 8th 05 02:08 AM


All times are GMT +1. The time now is 10:44 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017