Character Limitation formula help needed

[pstrucks]

[TABLE="width: 1573"]
<colgroup><col><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 4"]Maximum Length[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 5"]Product Information- User Input[/TD]
[TD="colspan: 3"]Generated SKU Code & Product Explanation[/TD]
[/TR]
[TR]
[TD]Product Category[/TD]
[TD]Product Name[/TD]
[TD]Attribute1[/TD]
[TD]Attribute2[/TD]
[TD]Attribute3[/TD]
[TD]Product Explanation[/TD]
[TD]Generated SKU Code[/TD]
[TD]Modified SKU Code[/TD]
[/TR]
[TR]
[TD]SAS PTPC[/TD]
[TD]5" Screw Philips head[/TD]
[TD]UM[/TD]
[TD="align: right"]5115[/TD]
[TD]xxxx[/TD]
[TD]5" Screw Philips head-UM-5115-xxxx[/TD]
[TD]5"SCREW-UM-511-XXX[/TD]
[TD]5"SCREW-UM-511-XXX[/TD]
[/TR]
</tbody>[/TABLE]

The formula for the SKU code generated is:
= UPPER(SUBSTITUTE(IF(RIGHT(SUBSTITUTE(SUBSTITUTE(CONCATENATE(LEFT(SUBSTITUTE(B13," ",""),CEILING.MATH($E$10/3)),"-",LEFT(SUBSTITUTE(C13," ",""),CEILING.MATH($E$10/7)),"-",LEFT(SUBSTITUTE(D13," ",""),CEILING.MATH($E$10/7)),"-",LEFT(SUBSTITUTE(E13," ",""),CEILING.MATH($E$10/7))),"--","-"),"--","-"),1)="-", REPLACE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(LEFT(SUBSTITUTE(B13," ",""),CEILING.MATH($E$10/3)),"-",LEFT(SUBSTITUTE(C13," ",""),CEILING.MATH($E$10/7)),"-",LEFT(SUBSTITUTE(D13," ",""),CEILING.MATH($E$10/7)),"-",LEFT(SUBSTITUTE(E13," ",""),CEILING.MATH($E$10/7))),"--","-"),"--","-"),LEN(SUBSTITUTE(SUBSTITUTE(CONCATENATE(LEFT(SUBSTITUTE(B13," ",""),CEILING.MATH($E$10/3)),"-",LEFT(SUBSTITUTE(C13," ",""),CEILING.MATH($E$10/7)),"-",LEFT(SUBSTITUTE(D13," ",""),CEILING.MATH($E$10/7)),"-",LEFT(SUBSTITUTE(E13," ",""),CEILING.MATH($E$10/7))),"--","-"),"--","-")),1,""),SUBSTITUTE(SUBSTITUTE(CONCATENATE(LEFT(SUBSTITUTE(B13," ",""),CEILING.MATH($E$10/3)),"-",LEFT(SUBSTITUTE(C13," ",""),CEILING.MATH($E$10/7)),"-",LEFT(SUBSTITUTE(D13," ",""),CEILING.MATH($E$10/7)),"-",LEFT(SUBSTITUTE(E13," ",""),CEILING.MATH($E$10/7))),"--","-"),"--","-"))," ",""))

I would like to be able to make a 12NC SKU code having xxxx-xxxx-xxxx but the current formula is only allowing the XXX on the final 3 stages of the SKU code that is generated. Does any one have an idea on how to alter the above formula to increase the character limit from xxx to xxxx?

 

[pbornemeier]

I could not find a reference for 12NC SKU code. There are likely rules that specify exactly how to create the code but without knowing them this answer is a guess. The only reference I could implied that 12NC stood for 12 Number Code ... which you are not generating (due to presence of letters)

Apparently the formula is in G13 and the Max length in E10.

Examining the formula...looks like formula enforces no double dashes and no periods. The existing formula limits each attribute to no more than 3 characters. I did not include that limit here. if that is a requirement then the formula can be changed or you can add data validation in columns C:E to limit the length of the input to 4 characters.

I believe this formula will do what you ask:

=UPPER(LEFT(SUBSTITUTE(SUBSTITUTE(B13," ",""),"-",""),$E$10-LEN(SUBSTITUTE(SUBSTITUTE("-"&C13&"-"&D13&"-"&E13," ",""),"--","-"))) &SUBSTITUTE(SUBSTITUTE("-"&C13&"-"&D13&"-"&E13," ",""),"--","-"))

Breaking down the major chunks

SUBSTITUTE(SUBSTITUTE("-"&C13&"-"&D13&"-"&E13," ",""),"--","-")

concatenates the attributes and a leading dash for each then removes the any spaces and replaces any double dash with a single dash. This will return a double dash if all attributes are blank. This is referred to as X in the next chunk explanation

LEFT(SUBSTITUTE(SUBSTITUTE(B13," ",""),"-",""),$E$10-LEN(X))

Remove spaces and dashes from the product name then take enough of the leftmost characters to make the total length of the SKU equal to the number in E10 or fewer.

If this does not produce the desired results please provide more sample inputs with desired outputs.