Running an e-commerce store is a lot of work for a business owner. Everything falls on your shoulders, from driving orders through marketing to fulfilling those orders. One of the biggest hassles when running an e-commerce store is filling the store with inventory. Luckily, there are many processes in place to help make this as smooth as possible. For example, sometimes vendors can provide you a catalog of new products to list on your site in CSV format. Other times, if you have a brick and mortar store, you can use connectors to link your POS or ERP inventory system to your e-commerce store to import your products that way.
While all of these solutions were created with the intention of making less work for the store owner, the remaining challenge is that the exported data from a vendor or a POS comes out in a CSV that seldom aligns with the format for CSV import to your e-commerce store. This leaves store owners in a pickle, because without the know-how to rearrange the data, they’re essentially at square one.
Also on StartupNation.com: Boost Your E-Commerce Business Branding
Let’s look a one method I use when restructuring e-commerce data in Google Sheets:
This is such a useful feature in Google Sheets, and the formula can be applied to so many situations. Once you have an understanding of the formula and how it works for your e-commerce data, it’s easy to remember and apply as the need arises.
Here’s an example: We have a client who wanted to list a product’s minimum order quantity and the product number as part of the description that would display on the product detail page. We had both pieces of this information in a spreadsheet provided by the vendor, as well as the item description. The challenge is that when we import this description into their e-commerce platform (such as Shopify), we want to import the description with both pieces of information already appended so we don’t have to manually add it later in the Shopify backend.
Here’s how to use a simple formula to combine all of this data, while adding some basic HTML so it renders spaced correctly once it’s imported:
In this example, we have the following e-commerce data:
Column B: The vendor supplied product description
Column C: The vendor supplied minimum quantity
Column D: The stock number
We want to combine these all into cell E2.
We start editing the value of the cell E2 by opening a formula with an equals, immediately followed by B2 to indicate that we want the contents of the cell B2 to be transposed into this cell.
To append additional information, we follow the cell number with an “&” symbol. Immediately following this, we include the exact information we want to appear after the value of cell C2. There’s two possible next steps: append the contents of another cell or append text (or html code). In our example, we’re doing the latter. If you are appending anything other than the contents of a cell, you must immediately open quotes after the “&” and include the specific content in there. In this example, I inserted a line break HTML tag, followed by another “&” to indicate that I’m appending more information. (I could have combined the “<br>” and “Minimum Quantity” both times they appear and it would work fine. I used this as an opportunity to demonstrate how to append text with other text).
When you want to append the value of another cell after this text, simply put the cell number that you want to transcribe the value of as we did with the start of the example. In this case, after displaying “Minimum Quantity,” I want to show the value of the minimum quantity for this product, which I have stored in cell C2, so I simply put C2 after the “&” in the cell. (It’s important to note that unlike text, when you are referring to the contents of a cell, you do not wrap the cell number in quotes. If you do, it will literally add that cell number as the value. In this example, if I wrapped “C2” in quotes, the value “C2” would appear instead of the number “7”).
This is a very simple and easy to remember formula equation that has infinite practical uses and can be used to manipulate thousands to tens of thousands of rows of product e-commerce data.
