Concatenate Help

[stte]

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?

 

[daniels012]

If I14 has Package Material and
I15 has Small
then try:

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



Michael

 

[debooo]

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]

=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]

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]

=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]

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]

Try:

Book1
	A	B	C	D
2	PackageMaterialBox	Small	P_MatB(S)
3	PackageMaterialWrap	Med	P_MatW(M)
4	PackageMaterial	Lar	P_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)&")"