Formula to check cell for a word (Text), if true return abbreviation for that word.

[ediejane]

Apologies in advance for the ambiguous title.

I am creating a sheet to automatically generate SKU's by combining product attributes.

[TABLE="width: 1000"]
<tbody>[TR]
[TD]Product Name[/TD]
[TD]Size[/TD]
[TD]Color[/TD]
[TD]Type[/TD]
[TD]Material[/TD]
[TD]Shape[/TD]
[TD]Quantity[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]2mm Aqua Glass Rhinestone Round[/TD]
[TD]2mm[/TD]
[TD]Aqua[/TD]
[TD]Rhinestone[/TD]
[TD]Glass[/TD]
[TD]Round[/TD]
[TD]3k[/TD]
[TD]2m-aq-rhresrd-3k[/TD]
[/TR]
</tbody>[/TABLE]

Columns for Size, Color, Type, Material, Shape and Quantity have dropdown lists with options for each attribute.

The selected option for each attribute is then trimmed to 2 or 3 characters (EXCEPT FOR SHAPE). The SKU is generated using CONCATENATE function of the trimmed characters.

For the Shape attribute, the list includes Round, Emerald, Marquis, Teardrop, Square, Rectangle. Trimming text will not work for this attribute. Instead, if "round" is selected from the list, I want the characters of "rd" in the SKU. If "Marquis" is selected I want the characters of "mq" in the SKU.

So what is the correct formula to do this? Is it an IF or MATCH or LOOKUP formula?

I hope this makes sense.

Thank you in advance for any help.

Regards,
EJ

 

[SteveO59L]

Re: Formula Help - Check cell for a word (Text), if true return abbreviation for that word.

tabulate your abbreviations and use vlookup()