Skip to main content

Remove unnecessary spaces from all SKUs

You may have become aware of this article because you received an email from us with the following text: "At least one product starts or ends with spaces in the SKU. Please revise the following products as described here."

You can solve this problem with the help of the Excel function "Trim". The function removes leading and following spaces from the text or, in our case, from the article numbers.

Procedure​

Step 1: Create help columns​

Create two new help columns in the cobby sheet by right-clicking on the column header above the name and selecting "Insert cells" (add new column).

Step 2: Name columns​

Give the columns unique names, e.g. "Trim 1" and "Trim 2".

Step 3: Insert trim formula (first column)​

Insert the following formula into the first new column ("Trim 1): =TRIM([@SKU])

Step 4: Insert trim formula (second column)​

Insert the following formula into the second new column ("Trim 2"):

=[@[Trim 1]] = [ @SKU ]

With this formula you compare the SKU and the column trim 1. If the value of the formula is false, the SKU contains blanks. If the value is "true", there are no spaces within the SKU.

Step 5: Save formula as template​

To be able to reuse these formulas at any time, save them as a template. To do this, click on the lower part of "Load products" -> Template... -> Save and enter a name for the template.

Step 6: Apply formula to all product rows​

Apply the formula to all product rows by selecting both cells and clicking on the lower right corner of the second cell.

Step 7: Check for duplicates​

Now that the SKUs no longer have spaces, you can check for duplicates. To do this, select the content of the first new column by clicking directly on the top of the column header. In the Data tab, select the button "Remove duplicates".

In the "Remove duplicates" dialog, select only the columns that you have already selected (trim 1).

If Excel finds duplicates, we need the SKU of the duplicate. If there are none, we are almost done and can move on to the next step.

Step 8: Convert data into values​

Now that you have only good SKUs in the first new column (i.e. no duplicates), you only need to convert the data into values so that they can be copied without creating a circular reference. To do this, select all contents of the trim 1 column, just as in step 5, right-click on the selection and select "Copy". Then go to the SKU column and right-click on it and select "Paste values".

Step 9: Save​

Click on "Save products" to finish editing the SKUs.