Calculated Field - Removing "Data"

[huntersoasis]

First let me describe the issue this database is not "Data normalization" but is required to be this way because of the way it is used in export to a MYSQL site.
so while data normalization would resolve the issue as far as the problem i have it would not work for the other direction.


A field in the database is a calculated field. and reads as this.

IIf([PRODUCT_MENU_ORDER]=9,"Out Of Stock",IIf([CATEGORY_1]="","",[CATEGORY_1]) & IIf([CATEGORY_2]="","","|"+[CATEGORY_2]) & IIf([CATEGORY_3]="","","|"+[CATEGORY_3]) & IIf([CATEGORY_4]="","","|"+[CATEGORY_4]) & IIf([CATEGORY_5]="","","|"+[CATEGORY_5]) & IIf([CATEGORY_6]="","","|"+[CATEGORY_6]) & IIf([CATEGORY_7]="","","|"+[CATEGORY_7]) & IIf([CATEGORY_8]="","","|"+[CATEGORY_8]) & IIf([CATEGORY_9]="","","|"+[CATEGORY_9]) & IIf([CATEGORY_10]="","","|"+[CATEGORY_10]) & IIf([CATEGORY_S_BIKINIHOUR]="","","|"+[CATEGORY_S_BIKINIHOUR]))

The issue that in place is at times there are "Categories" that can not be used while this calculated field takes 10 fields and creates them into one field with a option of another controlling it.

Can anybody think of a way to limit it to where if a "Category" field has the words "Dress" or "Shirt" it removes it while still completing the calcualation? can it be done in query? get me a idea. I did have a calculation that would work but ran into issue of a "Calculated field" can only have a calculation of 2048 char and the calculation that was written was well over 2048.

I will give you a idea of the field now.

CATEGORY_1,CATEGORY_1,CATEGORY_1,CATEGORY_1,CATEGORY_1,CATEGORY_1,CATEGORY_1,CATEGORY_1,CATEGORY_1,CATEGORY_1
Dress,Shirt,Pants,Black,Long,Short,Lace,Top,Bottom,Skirt,


and the Calculated Field would produce from the above if

[Product_Menu_Order

was not a 9
CALCATEGORY
Dress|Shirt|Pants|Black|Long|Short|Lace|Top|Bottom|Skirt

and what i want to happen is for somehow to remove in the calulated field "Long" and "Bottom" to make it


Dress|Shirt|Pants|Black|Short|Lace|Top|Skirt


Thanks for Any input.

 

[Joe4]

Cross-posted: Calculated Field - Removing "Data"

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).