Office hacks: Excel data column lossless merging and flexible combinations

When we try to merge or combine columns in Excel, in addition to the leftmost column, the data in other columns are generally lost, causing a lot of trouble. In fact, there is more than one way to merge cells, we can use the connector or connection function to merge cells, which not only can retain the original content, but also to achieve a flexible combination of combined content (such as adding custom spacing symbols or other information).

1. Combine data columns with the concatenation operator and ordinary punctuation

Excel in the connection symbol “&” can be connected to multiple cells, such as “= A3 & B3 & C3” can be 3 cells simply connected, but the actual use of diverse needs, not only this simple cell connected on the matter, but also need to be used in conjunction with the connector and separator, punctuation, etc.

Let’s take the example of expressing the sales of all appliances in a department in one cell (Figure 1). In order to merge the sales data from cells B to E into cell G in a textual representation, we need to add custom information based on the linker & and construct the formula as follows.

2015A-HEBING-1

= “Washing Machines”&B2&” Refrigerators”&C2&” Color TVs”&D2&” Air Conditioners”&E2

Note that you need to add a space before the middle name for the sake of clarity. Of course, you can also change the space to a stop sign.

After constructing the formula in the first cell (cell G2), the contents of the other cells in this column are automatically merged by pulling down the fill.

Tip: The above hyphenated operator & belongs to the function keyword category, must be half-angle characters; and user-defined characters (such as “washing machine” “refrigerator”, etc.), custom hyphen or punctuation (such as spaces, the pause), there are no specific provisions, with Any symbol can be used, half-angle or full-angle, but the user-defined characters need to be caused by half-angle double quotes, do not write full angle, otherwise there will be grammatical errors.

2. Use the join function to construct a formula to merge data columns

In addition to using the join character, we can also use the content join function CONCATENATE to achieve the cell join.

The basic syntax format of the CONCATENATE function is

=CONCATENATE(text1, [text2], …)

Among them, text1, text2 can be text or value, can refer to the contents of the cell, up to 255 items, note that items and items must be separated by a half comma.

For example, we want to automatically connect multiple sub-address segments into a complete address, you can use the formula “=CONCATENATE(A2,B2,C2,D2,E2)” (Figure 2). As A2 to E2 cells is the same line of consecutive cells, so the formula can also be simplified to “= CONCATENATE (A2: E2)”.

2015A-HEBING-2

For example, to achieve the above example of the combined expression of home appliances cells, the formula can be constructed as “= CONCATENATE (“washing machine”, B2, “, refrigerator”, C2, “, color TV”, D2, “, air conditioning”, E2)”, you can get the same results as the use of the connector & (Figure 3), while It also replaces the space separator with the more customary Chinese character “顿号”.

2015A-HEBING-3

Leave a Comment