CONCATENATE formulas combine strings of text. They can save substantial time when populating your image URLs or creating your Product Names.
In this article, we explain how to use CONCATENATE in Excel. The formula works the same way in other spreadsheet applications, like Google Docs.
What we'll cover:
- Using the CONCATENATE Formula
- A Faster Way to Enter Formulas
- Downloadable CONCATENATE Examples
- Related Articles
Using the CONCATENATE Formula
For our example, we'll look at automatically creating image URLs. Once you know how CONCATENATE works, you can get creative and use it to build Product Names, Product Descriptions, SKUs, and more.
- EXAMPLE: We'll assume you save your images as JPEGs. You name the main images as your <SKU>-1.jpg. The first alternate image is <SKU>-2.jpg, the second is <SKU>-3.jpg, and so on. If all of your images are hosted on the same site, the beginning of the URLs will always be the same, which is perfect for the formula.
1) Find the URL for one of your images. You'll need to copy the beginning of the URL.
2) Download the file from the Downloadable CONCATENATE Examples section of this article.
3) Open the example file in Excel. On the CONCATENATE Example 1 tab, clear all of the cells under the column headers.
4) Enter an SKU in the product custom sku column.
- If you needed to update the image URLs on all of your products, you could use the Retrieve Data button in the UPS Excel Add-In Tool. The retrieve would pull all of your SKUs.
5) Select the first cell of the image file column. Click on the Insert Function (fx) button to the left of the long formula bar in the ribbon.
6) Type or paste concatenate in the Search for a function field. Hit the Enter key on your keyboard to activate the Go button.
- CONCATENATE will be highlighted in the Select a function pane. Hit Enter again to activate the OK button.
7) Paste the beginning of your image URL in the Text1 field. Hit Tab to advance to the Text2 field.
8) Click on the cell containing your SKU. Hit Tab to advance to the Text3 field.
9) Paste or type -1.jpg. Hit Enter to activate the OK button.
- You will see a complete URL in the image file column now.
10) To enter your alternate image, select the first cell of the alternate image file 1 column. Repeat Steps 5 - 8. In Step 8, enter -2.jpg as Text3.
11) Copy the cell with the formula and paste it down the sheet next to all of your SKUs.
To copy the formula quickly, click on the fill handle of the cell and drag it down the sheet. The fill handle is the small black square located in the lower right-hand corner of the cell. You can also double click the fill handle to copy the formula down your entire sheet.
- As the formula copies down the sheet, the cell being referenced in the Text2 field automatically advances. Cell B2 becomes B3 in Row 3, and so on.
An Additional Consideration for Our Example:
If you organized your images into different folders or albums on your hosting site, your URL may contain the name of the folder or album. If so, you will need to adjust the URL in the Text1 field of your CONCATENATE. The URL path needs to contain the name of the folder or album.
- EXAMPLE: You organized your photos into two folders on your FTP: Guitars and Guitar_Accessories.
- For your guitars, the Text1 value in your formula would be: https://upsprodstandstor.blob.core.windows.net/sc70551025/Guitars/.
- For your accessories, Text1 would be: https://upsprodstandstor.blob.core.windows.net/sc70551025/Guitar_Accessories/.
A Faster Way to Enter Formulas
Once you are familiar with an Excel formula, you can enter it without using the help of the Insert Function pop-up.
1) Select the cell you want to add a formula to. Hit the = key.
2) Begin typing the name of your formula. Highlight the formula in the list that appears below your cell. Hit Tab or double click to select it.
3) For a CONCATENATE formula, paste the beginning of your URL as the text1 value, but this time hit use quotation marks around the URL.
- Quotation marks notify Excel that the text is part of the formula.
- To add a space between cells, you also need to use quotation marks. EXAMPLE: C2," ",D2.
Don't use quotation marks when entering a number without any letters.
4) Type a comma to advance to the text2 entry. Click on the cell containing your SKU.
5) Type a comma. Paste or type "-1.jpg" as your text3 value.
- EXAMPLE: The final formula will look like:
Downloadable CONCATENATE Examples
The CONCATENATE Example 1 tab contains an example of a formula to create a URL.
The CONCATENATE Example 2 tab contains the slightly more advanced example of a formula to create a Product Name.
Example 2 contains a column with the header _base name. When uploading spreadsheets, we ignore column headers that begin with an underscore.
Attachment from the Downloadable CONCATENATE Examples section: