Concatenate Help

stte

New Member
Joined
Feb 1, 2007
Messages
16
I want to create unique Index system base on Type and Size. E.g.
(in 2 separate columns)

Type = Package Material
Size = Small

The index formed should be P_Mat(S)

The requirement is that the first Capital of the Type followed by 3 Letters of the next WORD and followed by Bracketed first Letter of Size (S) or (M) or (L)

I cant seem to get the 3 letters of the second word

Help anyone?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
If I14 has Package Material and
I15 has Small
then try:

=LEFT(I14,1)&" "&MID(I14,FIND(" ",I14),4)&"("&LEFT(I15)&")"



Michael
 

debooo

Board Regular
Joined
Nov 2, 2006
Messages
86
i put the word type and the word size in cells c7 and c8 respectively.
in d7 and d8 i placed the information and then i used this formula.

=CONCATENATE(LEFT(D7,1),"_",CONCATENATE(MID(D7,FIND(" ",D7)+1,3)),"(",LEFT(D8,1),")")

let me know if it works for you

i assumed that all the words like "packing materiel" are all going to be different lengths so if you make it work just for that word it wont work for shorter or longer words. also i assume that you only have 2 words in each, im not sure it will work if you have more spaces.
 

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
=LEFT(A2,1)&"_"&MID(A2,FIND(" ",A2)+1,3)&"("&LEFT(B2,1)&")"
should do the trick, providing your items in column A all are two words only, with a single space between them. HTH. Larry
 

stte

New Member
Joined
Feb 1, 2007
Messages
16

ADVERTISEMENT

Thanks it works!

I just noticed something. Some category has 3 words instead of two. E.g.
Package Material Box
Package Material Wrap
Package Material

is it possible to still have 1 formula and return the following:

if it is 2 word, it will be P_Mat(S); and
if 3 word P_MatB(S) or P_MatW(S) ?

Thanks again!
 

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
=IF(ISERROR(FIND(" ",A2,FIND(" ",A2)+1)),LEFT(Sheet1!A2,1)&"_"&MID(Sheet1!A2,FIND(" ",Sheet1!A2)+1,3)&"("&LEFT(Sheet1!B2,1)&")",LEFT(Sheet1!A2,1)&"_"&MID(Sheet1!A2,FIND(" ",Sheet1!A2)+1,3)&MID(Sheet1!A2,FIND(" ",Sheet1!A2,FIND(" ",Sheet1!A2)+1)+1,1)&"("&UPPER(LEFT(Sheet1!B2,1))&")")
Where A2 has your type, and B2 has your size. HTH Larry
 

debooo

Board Regular
Joined
Nov 2, 2006
Messages
86
try this

=CONCATENATE(LEFT(D7,1),"_",CONCATENATE(MID(D7,FIND(" ",D7)+1,3)),IF(ISERR(FIND(" ",D7,FIND(" ",D7,1)+1)),CONCATENATE("(",LEFT(D8,1),")"),CONCATENATE(MID(D7,FIND(" ",D7,FIND(" ",D7,1)+1)+1,1),"(",LEFT(D8,1),")")))
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
Try:
Book1
ABCD
2PackageMaterialBoxSmallP_MatB(S)
3PackageMaterialWrapMedP_MatW(M)
4PackageMaterialLarP_Mat(L)
Sheet1


Formula in C2:

=LEFT(A2,1)&"_"&MID(A2,FIND(" ",A2),4)&IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",RIGHT(MID(SUBSTITUTE(A2," ","",1),FIND(" ",SUBSTITUTE(A2," ","",1)),2),1))&"("&LEFT(B2,1)&")"
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,113
Members
425,261
Latest member
JW00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top