Automatically Create SKU's from Product Phrases

[luxehouseofcouture]

I am in the process of creating a SKU generator for my Shopify products. I want the logic for the SKU's to follow the below template.


Example Product 1:
Vendor: Zutter
Name: Shirley Brushed Waffle Tunic
Color: Light Blue/Red/White
Size: X Large


Example SKU 1:
ZUTT-SBWT-LBRW-XL


Example Product 2:
Vendor: Love Tree
Name: Transition Cardigan
Color: Light Blue
Size: Small


Example SKU 2:
LOTR-TC-LB-S


Things to Note:

• Ideally, I would like to have the formulas reference the Codes tab and use the respective ID value for the adjacent matching value within the generated SKU's (i.e. the code for Booties is Btie).
• Product names variables should be generated as acronyms (i.e. Shirley Brushed Waffle Tunic would output as SSBW)
• Characters for each product variable should max at 4, using the first words in the text string to generate the variable acronym (i.e. Shirley Striped & Brushed Waffle Tunic would output as SSBW)
• In the event of a duplicate SKU, a numerical identifier should be appended to the duplicate (i.e. LT-TC-LB-S and LT-TC-LB-S-1)
• I have too many colors in my shop to create ID codes. So, we'll just need to generate an acronym to accomplish this. if a color is a phrase (i.e. Light Blue), the acronym would be LB. But if it is a multi-color option, the acronym should encompass all color options (i.e. Light Blue/Red/Green would be LBRG). I'd imagine that the formula needs to have an OR condition that looks for " " or "/" as a possible separator in order to accomplish this.
• Variables can be alpha-numerical and must be separated by hyphen's (-).

You can download the example excel sheet here.

 

[dave2018]

here is the updated one:

=UPPER(CONCATENATE(LEFT(CONCAT(LEFT(D2,1),IF(ISERROR(FIND(" ",D2)),MID(D2,2,3),MID(D2,SMALL(IF(MID(D2,ROW(INDIRECT("A1:A"&LEN(D2))),1)=" ",ROW(INDIRECT("A1:A"&LEN(D2)))+1),ROW(INDIRECT("A1:A"&LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))),1))),4),"-",LEFT(SUBSTITUTE(CONCAT(LEFT(B2,1),MID(B2,SMALL(IF(MID(B2,ROW(INDIRECT("A1:A"&LEN(B2))),1)=" ",ROW(INDIRECT("A1:A"&LEN(B2)))+1),ROW(INDIRECT("A1:A"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),1)),"&",""),4),"-",IF(ISERROR(FIND("/",I2)),LEFT(I2,3),CONCAT(IF(NOT(ISERROR(FIND(" ",I2))),LEFT(I2,1),LEFT(I2,1)),IFERROR(MID(I2,FIND(" ",I2)+1,1),""),MID(I2,SMALL(IF(MID(I2,ROW(INDIRECT("A1:A"&LEN(I2))),1)="/",ROW(INDIRECT("A1:A"&LEN(I2)))+1),ROW(INDIRECT("A1:A"&LEN(I2)-LEN(SUBSTITUTE(I2,"/",""))))),1))),"-",INDEX(Codes!$M:$M,MATCH(K2,Codes!$L:$L,0))))

Remember, CTRL+SHIFT+ENTER in O1.



ps. In I3, lets say you have "Light Red/White/Blue/Black" without the quotes, do you want LRWBB (to include ALL the colors?) or just LRWB (the first 4?).

if the answer Is LRWBB, use the above code. If you want LRWB, use this code:

=UPPER(CONCATENATE(LEFT(CONCAT(LEFT(D2,1),IF(ISERROR(FIND(" ",D2)),MID(D2,2,3),MID(D2,SMALL(IF(MID(D2,ROW(INDIRECT("A1:A"&LEN(D2))),1)=" ",ROW(INDIRECT("A1:A"&LEN(D2)))+1),ROW(INDIRECT("A1:A"&LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))),1))),4),"-",LEFT(SUBSTITUTE(CONCAT(LEFT(B2,1),MID(B2,SMALL(IF(MID(B2,ROW(INDIRECT("A1:A"&LEN(B2))),1)=" ",ROW(INDIRECT("A1:A"&LEN(B2)))+1),ROW(INDIRECT("A1:A"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),1)),"&",""),4),"-",LEFT(IF(ISERROR(FIND("/",I2)),LEFT(I2,3),CONCAT(IF(NOT(ISERROR(FIND(" ",I2))),LEFT(I2,1),LEFT(I2,1)),IFERROR(MID(I2,FIND(" ",I2)+1,1),""),MID(I2,SMALL(IF(MID(I2,ROW(INDIRECT("A1:A"&LEN(I2))),1)="/",ROW(INDIRECT("A1:A"&LEN(I2)))+1),ROW(INDIRECT("A1:A"&LEN(I2)-LEN(SUBSTITUTE(I2,"/",""))))),1))),4),"-",INDEX(Codes!$M:$M,MATCH(K2,Codes!$L:$L,0))))

please note, that this doesnt catch doubles yet.

also, if there are thousands of entries, it may slow calculation time, because there is the volatile INDIRECT function. pls reply if this is an issue you want me to work around.

 

[dave2018]

edit: mistake

 

[luxehouseofcouture]

here is the updated one:

=UPPER(CONCATENATE(LEFT(CONCAT(LEFT(D2,1),IF(ISERROR(FIND(" ",D2)),MID(D2,2,3),MID(D2,SMALL(IF(MID(D2,ROW(INDIRECT("A1:A"&LEN(D2))),1)=" ",ROW(INDIRECT("A1:A"&LEN(D2)))+1),ROW(INDIRECT("A1:A"&LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))),1))),4),"-",LEFT(SUBSTITUTE(CONCAT(LEFT(B2,1),MID(B2,SMALL(IF(MID(B2,ROW(INDIRECT("A1:A"&LEN(B2))),1)=" ",ROW(INDIRECT("A1:A"&LEN(B2)))+1),ROW(INDIRECT("A1:A"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),1)),"&",""),4),"-",IF(ISERROR(FIND("/",I2)),LEFT(I2,3),CONCAT(IF(NOT(ISERROR(FIND(" ",I2))),LEFT(I2,1),LEFT(I2,1)),IFERROR(MID(I2,FIND(" ",I2)+1,1),""),MID(I2,SMALL(IF(MID(I2,ROW(INDIRECT("A1:A"&LEN(I2))),1)="/",ROW(INDIRECT("A1:A"&LEN(I2)))+1),ROW(INDIRECT("A1:A"&LEN(I2)-LEN(SUBSTITUTE(I2,"/",""))))),1))),"-",INDEX(Codes!$M:$M,MATCH(K2,Codes!$L:$L,0))))

Remember, CTRL+SHIFT+ENTER in O1.

Hi @dave2018. You're amazing. This formula is working wonders. I went ahead and used the top code to create an acronym for all colors. There is one change I am trying to make, though, to help ensure less duplicate values. When there is one color with multiple words (i.e. Light Teal Blue), the formula is currently converting it to LIG. I would rather it convert to LTB instead. What would I have to adjust to make this happen?

 

[luxehouseofcouture]

Bumping this post to see if @dave2018 has the time to help me make the above mentioned modification to this excel formula, or anyone else with the know how for that matter.

Thanks in advance for the help!

 

[luxehouseofcouture]

Hi @dave2018 - just wanted to give you one more bump to see if you can/are willing to help me finish this formula. I did some research and I was able to find another formula that creates the acronym based on capital letters found within a string of text, and it worked perfectly, but the problem is that it doesn't pull the first three characters for one word phrases like yours does. I tried combining your formula with the formula I found, but I do not have the Excel prowess to make it work like I need. Would you be able to piece these two together for me, or modify yours to make acronyms out of multi-word colors? Please let me know either way so I'm not just waiting and hoping. Thank you so much!

Here is the formula I found. It needs to be submitted using Ctrl+Shift+Enter, just as you instructed me to do so.

=TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)),ROW(INDIRECT("63:90")),0)),MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),""))