Advanced - Merging table columns in Excel

For example if we have a company selling books with an existing database that uses the following fields

Author(s), ISBN Number, Cover, Dimensions, Pages

Because the Zeald website database does not include fields for any of these values, we may combine them all together and include them as part of the Product description field that does exist in the Zeald website database.

Basic Concatenate

1. Insert new column entitled New Product Description
2. Select the first cell in the New Product columnfor the first product row
3. Select the Insert functionbutton
4. Select CONCATENATEfrom the prompt window
5. Now select the different cells in the same product row to make up the new combined result, ensure that they are selected in the correct order they should appear on the website.

Each table cell is combined one after the other in the order as specified by the formula without any separation, so the result for the New Product description will display on the website as follows:

Obviously this display is difficult to read and we are missing the labels which tell the user what each of the values mean. We need to add the different labels to our formula and associate them with the different values: Strings of text may be added to the result by incorporating them into the formula. Text strings are defined by enclosing each string with quote marks and separated with commas

=CONCATENATE("Author(s): ", A2," ISBN Number: ",B2," Cover: ", C2," Dimensions: ", D2," Pages: ",E2,F2)

Each table cell is combined one after the other in the order as specified by the formula Each value is separated with the text as specified, so the final result for the New Product description will appear on the website as follows:

Author(s): Anita Jeram ISBN Number: 0744592011 Cover: Hard cover Dimensions: 235 x 270mm Pages: 28 This little bunny loves the magical places he goes to in his story book
 

Concatenate with additional text & HTML

You will note that the result is still very difficult to read. We now need to separate each of the fields with line breaks and differentiate the label from the value using text formatting.

This time we add HTML tags to specify the desired format and line breaks:
Some basic HTML tags include:

BOLD = Author:
ITALIC = Author:
LINE BREAK =

It is a good idea to test you HTML by coping the HTML from the result cell and pasting it into the Rich Text Editor, in HTML mode. When you view it in Normal mode you will get an idea for how the final HTML will display when the spreadsheet has been uploaded.

For more information about the Excel CONCATENATE formula please refer to the Excel help. If you require help setting up your spreadsheet, Zeald would be happy to help out but there wis an hourly cost associated with this. Please call 09 415 7575