MS Excel Concatenate Tutorial
The following post is to test Concatenate formulas in Microsoft Excel with added HTML to import vendor data from excel spreadsheets. I am going to include several examples that can be used depending on the provided info. If you are familiar with Excel you will know the variables called out the in the formula reference each cell in an excel spreadsheet. I also need to use quotes around any text, html, or spaces I want to be included in the output. The list is delimitated by commas and enclosed in parenthesis.
CONCATENATE Syntax
=CONCATENATE(E6," ",G6," ",H6," ",I6," ",T6," ")
I’m going for something like this:
The product description appears first above the features and the fabrics, this data appears in cell E6 in my test spreadsheet.
FEATURES
- Feature 1
- Feature 2
- Feature 3
FABRICS
- Fabric
The HTML will look like this:
The Excel Concatenate function will look like this:
Formula for 1 feature & 1 fabric
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li> ",T6," </li></ul> ")
Formula for 1 feature & 2 fabrics
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li> ",T6," </li><li> ",U6," </li></ul> ")
Formula for 1 feature & 3 fabrics
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li> ",T6," </li><li> ",U6," </li><li> ",V6," </li></ul> ")
Formula for 2 features & 1 fabric
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li><li> ",H6," </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li>",T6," </li></ul> ")
Formula for 2 features & 2 fabrics
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li><li> ",H6," </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li>",T6," </li><li> ",U6," </li></ul> ")
Formula for 2 features & 3 fabrics
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li><li> ",H6," </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li> ",T6," </li><li> ",U6," </li><li> ",V6," </li></ul> ")
Formula for 3 features & 1 fabric
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li><li> ",H6," </li><li> ",I6," </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li>",T6," </li></ul> ")
Formula for 3 features & 2 fabrics
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li><li> ",H6," </li><li> ",I6," </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li>",T6," </li><li> ",U6," </li></ul> ")
Formula for 3 features & 3 fabrics
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li><li> ",H6," </li><li> ",I6," </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li> ",T6," </li><li> ",U6," </li><li> ",V6," </li></ul> ")
Formula for 12 features & 1 fabric
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li><li> ",H6," </li><li> ",I6," </li><li> ",J6," </li><li> ",K6," </li><li> ",L6," </li><li> ",M6," </li><li> ",N6, " </li><li> ",O6," </li><li> ",P6," </li><li> ",Q6, " </li><li> ",R6, " </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li> ",T6," </li></ul> ")
Formula for 12 features & 2 fabrics
=CONCATENATE(E6," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G6," </li><li> ",H6," </li><li> ",I6," </li><li> ",J6," </li><li> ",K6," </li><li> ",L6," </li><li> ",M6," </li><li> ",N6, " </li><li> ",O6," </li><li> ",P6," </li><li> ",Q6, " </li><li> ",R6, " </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li> ",T6," </li><li> ",U6," </li></ul> ")
Formula for 12 features & 3 fabrics
=CONCATENATE(E197," <p> </p><h3><strong>FEATURES</strong></h3><ul><li> ",G197," </li><li> ",H197," </li><li> ",I197," </li><li> ",J197," </li><li> ",K197," </li><li> ",L197," </li><li> ",M197," </li><li> ",N197, " </li><li> ",O197," </li><li> ",P197," </li><li> ",Q197, " </li><li> ",R197, " </li></ul><p> </p><h3><strong>FABRICS</strong></h3><ul><li> ",T197," </li><li> ",U197," </li><li> ",V197, "</li></ul> ")
This reference list should work for my current Columbia spreadsheet. Keeping in mind the letter will be the same but the numbers will change depending on the row they need to apply to. I will add the formula in row 6 and then I will copy the formula down for each row on the spreadsheet.
Tip: Excel will color code selected cells, if the cell ID is not colored this is an indication of where you made a mistake in your formula.