Concatenate Help

stte

New Member
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
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
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
=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

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
=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
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
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)&")"

Replies
2
Views
262
Replies
14
Views
227
Replies
2
Views
346
Replies
6
Views
434
Replies
5
Views
187

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

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.

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

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