How to merge the contents of cells of different examples together in Excel and separate them with ca

Open the word document, you can see that there are paragraph markers.

Click the "File" button in the upper left corner to enter the file column and set it.

After entering the file, find and click "Options" in the bottom left corner to enter the Word settings interface.

After <> enter the word option, in the menu bar on the left, find the second option "Display" and click the "Display" button.

Enter the display column interface, in the middle, you can find an option "paragraph marker", remove the " in front of it and click "OK" below to close the setting.

After closing the settings, return to the Word document interface, and you can find that all the carriage return characters are missing.

This problem cannot be achieved with a function formula, and VBA must be used.

Copy to the area of the worksheet where the original data is located, and after running, a new table will be automatically added, and the merged data will be written out in the new table.

sub Merge column().

dim d, rowi as long, sh as worksheet

set d = createobject("")

rowi = range("a65536").end(xlup).row

for i = 2 to rowi

if , 1).value)) then

elsed(cstr(cells(i, 1).value)) = cells(i, 2)

end if

nextset sh =

with sh

cells(2, 1).resize(, 1) =

cells(2, 2).resize(, 1) =

cells(1, 1) = "columns"

cells(1, 2) = "Merge data"

end with

set d = nothing

set sh = nothing

end sub

Add &char(10)& between the two paragraphs

For example, if you want to merge cells A1 and A2 and divide them into two rows, you can do it.

a1&char(10)&a2

If you add text, you need to add text in"", for example.

a:"&a1&char(10)"b:"&a2 shows. A: A1 cell contents.

B: A2 cell contents.

Preparation Tools Materials: A computer with Windows 10, Microsoft Office Home and Student Edition 2016 Excel software.

1. First of all, enter several sets of data in the open excel**, which need to be merged into one cell, separated by commas.

2. Enter the merge formula in cell C1: =A1&", b1, you need to enter double quotation marks in the English input method.

3. Click Enter to merge the data to be merged and add a comma to the data.

4. Then click the formula of cell C1 to fill in and copy downward, and you can merge the data and add commas in batches.

b1=a1b2=b1&"、a2

Scroll down to the last row where the data is listed in A.

The last row of column b is what is sought.

1 Enter =zx()+enter in any cell in the worksheet, all cell data in column A will be automatically combined and separated by a separator.

2 I don't understand hi me.

I think it's still convenient to use "merge them one by one". Such as:

B1 input. =a1&"、a2&"、a3&"、a4&"、a5&"、a6&"、a7

Or. =concatenate(a1&"、a2&"、a3&"、a4&"、a5&"、a6&"、a7)

If the area of column A is too large, it will be troublesome to enter the formula, you can use the F9 function key:

1. Enter the equal sign in b1 = - select a1 a100 (assuming 100 rows) - do not enter - press f9 - delete "=" and "{} directly in the column

2. Select B2 - Ctrl+H - Find Content:

Replace with: leave blank and do not enter anything.

Replacement; 3. Find content:

Replace with: —Replace.

Custom functions, see the attachment.

Are you going to connect all the contents of column A in a cell B1 with a ton?

Consider one of two approaches to this problem:

Method 1: Pivot table plus textjoin function method:

Insert a pivot table, group and sum the quantities for the names and sales categories, and then use the formula to find the sales day aggregation. In the pivot table, drag the name and category to the row field area, and the quantity to the summary area, and set the format to the way you need. As shown in the figure, put the pivot table in the cell starting from g1, after the pivot table is set, then use the formula in column j to get the aggregation of the sales day:

Drop down <>

Method 2, use power qurey, complete it at one time, it is recommended. After the cursor turns the data area, the data menu, get the data - from ** or the region, the import data dialog box pops up, and then enter the power qurey, in the pq, it only takes one step to achieve the desired effect, it is grouped, and the group is selected as advanced, and the name and type are used as the classification basis, the quantity is summed, and the sales day is also selected first to sum (later changed to aggregation by modifying the m statement). Once determined, the quantities are summed, but the sales day is text data, and of course the summing will be wrong.

Open Excel**, select the column with the same item, click Sort & Filter, and select Ascending.

Please click Enter a description.

Select to sort the same items together in the current sequence, click the data at the top of the page, and click OK after viewing the classification summary.

Please click Enter a description.

Click on cells in the same category, then click Merge to Center, then click OK, and repeat until all the same cells are categorized.

Please click Enter a description.

It is summarized as follows. > click Enter a description end

1.In the auxiliary column, use the textjion function to combine the name and category <>

2.Copy the auxiliary column, right-click, and paste it as a numeric value.

3.Select Secondary Columns, Data, Remove Duplicates.

4.Select the auxiliary column, data, column, and column by comma to obtain two columns of name category 5 in Figure 4Summation using sumifs.

6.Use the textjion function to combine dates.

1. G2 input.

index(a:a,min(if(countifs($g$1:$g1,$a$2:

a$100,$h$1:$h1,$b$2:$b$100),4^8,row($2:

Array formula, do not enter after entering, press Ctrl+Shift+Enter to end the calculation, then fill to the right to h2, and then fill down together.

2. I2 input.

sumifs(c:c,a:a,g2,b:b,h2) enter and fill down.

3. J2 input.

Array formula, the operation is the same as 1, and then fill down.

Box select the cells you want to merge and select Center after Merge, select the Merge Cells option, the following are the detailed operation steps:

Box select the cell.

After opening the required excel**, select the cells that need to be merged.

Click Merge and center.

Once the cells are selected, click on the Merge and Center option in the Start ribbon.

Click Merge Cells.

After opening the pop-up window, select the Merge Cells column in the box, and click on it, and the selected cells will be merged.

After splitting, use the targeting, padding. It's a question of selecting blank cells and making each cell display the same content.

Steps: 1. Take excel2016 as an example to establish **.

2. Select all the cells that need to be split.

3. Click "Center after Merge".

4. Split the cells.

5. Click the "Find & Select" drop-down arrow and select "Targeting".

6. Select Null Value.

7. The empty cell in the split cell is selected.

8. Enter "=a1".

9. Press and hold the Ctrl key + press enter on the keyboard to complete the filling.

How to merge the contents of two cells together:Tools Raw materials: Mechanical Revolution S3 Pro, Windows 10, Excel 2021

1. Enter an equal sign.

Open Excel and enter " " " in a blank cell to select the first cell.

2. Merge cells.

Enter the connection symbol in the formula, select the second cell.

3. Copy the formula.

Double-click the cell to copy the formula on the whole column, and the contents of the two cells are merged together.

Here's how to merge multiple cell contents together:

Tools Raw Materials:

Computer: Lenovo computer Tianyi 510s.

System: Windows 10.

Software: Excel2016.

1. Merge the contents of cells A2, B2, C2 into cell D2.

2. Enter the formula in cell d2.

3. The content is merged together, which is used as a connector in Excel, and the contents of the two cells are merged together after using the connector between two cells.

4. Fill the formula downwards to quickly get the merged data.

5. Enter the formula concatenate(a2,b2,c2) in cell e2.

6. Confirm and fill the formula downward, also merge the contents of the first three columns of cells together, in Excel, the function of the symbol and the concatenate function is basically the same, I personally think that the connector is more convenient.

7. Enter the formula phonetic(A2:C2) in F2 cell.

8. The result obtained after filling down is exactly the same.

The concatenate function merges cells.

1. Definition of the concatenate function.

Merge several text strings into a single obscure text string.

Grammar. concatenate (text1,text2,..

text1, text2, .is between 1 and 30 text items that will be combined into a single text item. These text items can be text strings, numbers, or references to individual cells.

2. Usage: Suppose you want to merge the contents of A1 and B1 cells in C1 cell, then click C1 and write the formula in C1 cell:

concatenate(a1,b1)。

3. The specific operation is as follows:

1) Enter the formula in cell D2.

2) Select the macro to select the cells you want to merge, click from the cells that need to be merged, merge d2 here, and click on hunger.

3) Enter the formula =concatenate(a2,b2,c2)

4) Confirm and fill down the formula, likewise merge the contents of the first three columns of cells together.

As shown in the figure below, it is an employee information table of a unit, and now the boss asks to merge the same department cells of the department column together.

Please click Enter a description.

Please click Enter a description.

Click Merge Conversions.

Please click Enter a description.

Click Merge the same cells.

Please click Enter a description.

In the pop-up dialog box, enter the range of cells you want to merge in the Area option, and then click OK.

Please click Enter a description.

end2. Pivot table method.

This method is complicated, so be patient!Insert a secondary column at the end of ** and fill the auxiliary column with an ordinal number. and sort ** in ascending order by department column.

Please click Enter a description.

Click **Any cell, then click Insert, PivotTable, leave all default options unchanged in the pop-up Create PivotTable dialog box, and click OK.

Please click Enter a description.

Drag the Department column, and then the secondary column field to the row area.

Please click Enter a description.

Click Design, click Subtotal, select Don't Show Subtotals under it, click Disable for rows and columns under Totals, and then click Show as ** under Report Layout.

Please click Enter a description.

Click any cell of the pivot table, right-click the mouse, select the pivot table option, click Layout & Format, check the box and center the cells with labels, and click OK.

Please click Enter a description.

Select the range of cells where the merged cells are located, click Format Painter, and click the A2 cell in the data source table.

Please click Enter a description.