1. Solid Commerce
  2. Microsoft Excel Add-In Tool
  3. Using Excel

Combining Text in Spreadsheets Using the CONCATENATE Formula

Avatar
Josh Smith
Follow

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:

  1. Using the CONCATENATE Formula
  2. A Faster Way to Enter Formulas
  3. Downloadable CONCATENATE Examples
  4. 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 Solid Commerce 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.

excel_add-in_insert_function_button.png

 

6) Type or paste concat 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.

excel_add-in_insert_function_search_concatenate.png

 

7) Paste the beginning of your image URL in the Text1 field. Hit Tab to advance to the Text2 field.

excel_add-in_insert_function_concatenate_text1_image_url.png

 

8) Click on the cell containing your SKU. Hit Tab to advance to the Text3 field.

excel_add-in_insert_function_concatenate_text2_sku.png

 

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.

excel_add-in_concatenate_image_file_url.png

 

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.

excel_add-in_concatenate_alternative_image_url.png

 

11) Copy the cell with the formula and paste it down the sheet next to all of your SKUs.

  • TIP: 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.

excel_add-in_fill_handle.png

 

  • 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.

excel_add-in_concatenate_formula_advancing_cell.png

 

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: http://images7.solidcommerce.com/sc70545/Guitars/.
    • For your accessories, Text1 would be: http://images7.solidcommerce.com/sc70545/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.

excel_add-in_no_insert_function_formula_selection.png

 

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.
  • NOTE: Don't use quotation marks when entering a number without any letters.

excel_add-in_no_insert_function_beginning_url.png

 

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:

excel_add-in_no_insert_function_complete_example.png

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.

  • NOTE: Example 2 contains a column with the header _base name. When uploading spreadsheets, we ignore column headers that begin with an underscore.

Related Articles

 

Back to top

 

Attachment from the Downloadable CONCATENATE Examples section:

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.