Hi @RRIBEIROADM ,
To automate the standardization of your product descriptions, the most effective method is using Power Query, an integrated tool within Excel. It allows you to build a repeatable transformation process that acts as a "calculation memory," which can be refreshed with a single click without needing VBA.
First, you must load your data into the Power Query Editor. Select your table of products, navigate to the Data tab on the Excel ribbon, and click on From Table/Range. This action opens the Power Query Editor, where all transformations will be performed.
Within the editor, you'll extract the required information into new columns. The most intuitive way to do this is with the Column From Examples feature, found under the Add Column tab. To extract the BRAND, start a new column from examples and type the brand name (e.g., "Ypรช", "Veja") for the first few rows. Power Query will learn the pattern and auto-fill the rest. Repeat this exact process to create a SIZE column by typing examples like "500ml" and "2L". To extract the FUNCTION, select the original product description column, go to the Home tab, and use Split Column > By Delimiter. Choose a hyphen (-) as the delimiter and split at the "left-most delimiter." This will separate the product type from its function. The newly created column will contain the function description.
Finally, organize your table for the final output. Rename the columns you created to match your desired headers: TYPE OF PRODUCT/SIZE, FUNCTION, and BRAND. You may need to merge the extracted product type with the SIZE column to create the TYPE OF PRODUCT/SIZE column. Remove any intermediate or redundant columns by right-clicking them and selecting "Remove." Reorder the columns into the desired final sequence. Once complete, click Close & Load from the Home tab. This will load the standardized table into a new worksheet in Excel.
The entire process is recorded step-by-step in the Applied Steps pane within the Power Query Editor. This pane serves as the complete evidence and calculation memory of how the standardization was performed. If you add new products to your original source table, you can simply right-click the new, standardized table and select Refresh to apply all the same transformations automatically.
Best regards,
