Creating Title Lengths on Data Sheets

If you have a limited character count for your product titles, here we go over how to implement that in your data sheet by first counting the characters in each title, then creating a formula to trim the title down.

1. In the second box of a new and blank column, (the first box can be ignored since that’s normally the column title itself) begin to type “=len”, then double-click on the LEN dropdown that will come up. Now click on the cell that you want, close the parentheses and hit the enter key on your keyboard. Once you hit enter, you’ll see it has the character count for that box.

2. Now you’ll want to copy/paste the formula from that cell into the rest of the column. This is done by copying that second cell with the formula. Then scroll to the bottom of your data sheet, and while holding down the shift key, click on the last box in that column that will hold a value from your data sheet. Then right click and paste. You’ll then see the column fill up with the character count for each respective row.

3. Now you’ll want to paste that whole column into a new one. Highlight the column with the formulas and copy it, then highlight a new column and right click to “Paste Special”. In the pop up window, select “Values”, and click “OK”.

4. You can now delete the column with formulas.

5. Next you’ll want to trim down the titles that are over your character limit. You’ll need to repeat Steps 1 - 4, but with a different formula. The formula used here will be “=LEFT(Concatenate([box1],[character limit])”. Here is an example of the formula implementation:

6. You’ll notice that the title will be cut off exactly after the character count, so some words might be cut off, or even cut off mid-word. You can go through and fix them up so that they’re not such abrupt endings. You can even get creative if you have the time and use the filter on titles with characters over your limit and edit them manually before using the formula.

7. When you are finished, remember to add a header title in the first box of your trimmed column. You can then either delete the old title column, or keep it and just ignore during import mapping so the new title column is read instead.