Questions? Feedback? powered by Olark live chat software

Advanced - Merging table columns in Excel

 

When trying to match an existing database format with the Zeald database format it is a common requirement to combine different fields using 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.

The following guide outlines how we may combine these different fields together to form just one value. We also explain how to add basic formatting to display this information in a user friendly manner with HTML tags.

This method of combining fields in an excel table is known as Concatenating

For the following example product I Love My Little Story Book we are going to add a new column to the excel table and entitle it New Product description
 
 
A
B
C
D
E
F
G
1
Author(s)
ISBN Number
Cover
Dimensions
Pages
Product Description
New Product description
2
Anita Jeram
0744592011
Hard cover
235 x 270mm
28

This little bunny loves the magical places he goes to in his story book

 
 
We need to add all of the values of each column for each product row. We do this using the following Concatenate formula:

Basic Concatenate

Ensure that the table cell properties are set to General to ensure that the result will display for the cell formulas.

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.

=CONCATENATE(A2 ,B2, C2, D2, E2, F2)

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:

Anita Jeram0744592011 Hard cover235 x 270mm28This little bunny loves the magical places he goes to in his story book.


Concatenate with additional text

 

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:

Authors: 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 = <B>Author: </B>
ITALIC = <I> Author: </I>
LINE BREAK = <BR/>

=CONCATENATE("<b>Author: </b>",A2,"<br/><b>ISBN Number: </b>", B2, "<br/><b>Covers: </b>",C2, "<br/><b>Dimensions: </b>",D2, "<br/><b>Pages: </b>",E2,"<br/><br/>",F2)
 
Authors: 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
 

Please note: the result for the formula will display the HTML code, you will not be able to view the final format until the spreadsheet is uploaded.

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.

 



Once you are happy with the final formula, it is a simple operation to apply it to the remainder of the table rows as follows:Select the small black square that appears in the bottom right hand corner of the selected cell and drag it down the entire column. You will not that all of the rows will update themselves with the new results.

 

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