Home > Article > Computer Tutorials > How to merge the contents of two cells into the contents of one cell
For example: The content of cell A1 is: Teacher
The content of cell B1 is: Hello
You want to merge the contents of cells A1 and B1 into: Hello teacher, then the formula of C1 is: =A1&B1
That’s it!
If you have entered two cells and want to merge the contents, then you can follow my method below:
Everyone knows that in EXCEL, when multiple cells are merged into one cell, the merge function of EXCEL can only retain the contents of one cell in the upper left corner, and the contents of other cells will be discarded. Sometimes we need to keep all the merged contents into one cell. Below is a VBA macro I made to achieve this function.
First press the ALT F11 key combination to open the VBA window, press the top menu "Insert-Module" to insert a module, and copy the following code into it:
Sub merge 1()
Application.DisplayAlerts = False
Set tt = Selection 'Assign selected area
a = tt.Rows.Count 'Number of selection rows
x = tt.Row 'The first row number
y = tt.Column 'Column number
s = tt.Columns.Count - 1 'Decrease the number of columns by 1
For j = x To x a - 1
For i = 1 To s
Cells(j, y) = Cells(j, y) & Cells(j, y i)
Next
Range(Cells(j, y), Cells(j, y s)).Merge 'Merge
Next
Application.DisplayAlerts = True
End Sub
Sub merge2()
t = """
Set tt = Selection
x = tt.Row 'The first row number
y = tt.Column 'Column number
For Each a In Selection
t = t & a.Value
a.Value = """
Next
Cells(x, y) = t
Selection.Merge
Selection.WrapText = True 'Automatically wrap lines
End Sub
After copying, close this window, return to EXCEL, press AIT F8 again, and click Execute!
Hope it can be of some help to you!
Sub aa()
'Sort
arr = Range("D3:M9").Value
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2) - 1
For k = j 1 To UBound(arr, 2)
If arr(i, k)
tmp = arr(i, j)
arr(i, j) = arr(i, k)
arr(i, k) = tmp
End If
Next
Next
Next
For i = 1 To UBound(arr, 1)
x = Cells(i 2, 2)
f = False
s = arr(i, 1)
For j = 1 To UBound(arr, 2)
If arr(i, j) = x Then f = True 'Whether to fill in red
If j > 1 Then 'Remove duplicates
If arr(i, j) arr(i, j - 1) Then
s = s & "," & arr(i, j)
End If
End If
Next
Set rg = Range("N" & (i 2))
rg.Value = s
If f Then rg.Interior.ColorIndex = 3
Next
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
twenty one
twenty two
twenty three
Sub merge cells automatically and()
Application.ScreenUpdating = False
j = Range("F"& Rows.Count).End(3).Row
Range("G3:G"& j).UnMerge
Range("G3:G"& j).ClearContents
n = Range("F3")
m = 3
Fori = 4 Toj
IfRange("B"& i) = ""Then
n = n Range("F"& i)
Else
Range("G"& i - 1) = IIf(n = 0, "", n)
Ifm
n = Range("F"& i)
m = i
EndIf
Next
Range("G"& i - 1) = IIf(n = 0, "", n)
Ifm
Application.ScreenUpdating = True
EndSub
Open the excel document that needs to be merged, and find the object cell.
Use character connectors:
Use the character connector "&" to connect. This method is used when merging data from a few cells. For example: B1=A1&A2;B1=A1&A2&A3, one push. See the picture for specific operations.
Use text functions:
Use the text function CONCATENATE() to operate, for example: CONCATENATE(A1,A2,A3,......A255), but: the CONCATENATE function can concatenate up to 255 text strings into one text string . Join items can be text, numbers, cell references, or a combination of these. For example, if your worksheet contains a person's first name in cell A1 and his or her last name in cell B1, you can combine the two values into another cell by using the following formula.
The specific operations are as shown in the figure.
Function usage instructions:
CONCATENATE(text1, [text2], ...)
CONCATENATE function syntax has the following parameters (Parameters: values that provide information for an operation, event, method, property, function, or procedure.):
Text1 Required. The first text item to be concatenated.
Text2, ... optional. Additional text items, up to 255 items. Terms must be separated by commas.
Note You can also use the ampersand (&) calculation operator instead of the CONCATENATE function to join text items. For example, =A1 & B1 returns the same value as =CONCATENATE(A1, B1).
Use information function:
PHONETIC () operates to obtain a string representing pinyin information. It is mainly used for Japanese. It cannot use numbers. The specific operation is shown in the figure.
Information function description:
PHONETIC(reference)
PHONETIC function syntax has the following parameters (Parameters: values that provide information for an operation, event, method, property, function, or procedure.): Reference Required. A text string or reference to a single cell or range of cells containing a furigana text string.
Description: If reference is a cell range, return the furigana text string in the upper left corner cell of the range. If reference is a range of non-adjacent cells, the error value #N/A will be returned.
Merging multiple data types:
This method mainly combines character data content with numeric data.
1. If numeric data and character data are to be merged, you must first format the numeric data to be merged and convert it to character type (text type).
2. The PHONETIC (reference) function can be operated using the data area, which is more convenient.
3. The CONCATENATE(text1, [text2], ...) function must be added cell by cell.
4. The character connector "&" must be used in each merged cell.
Use VBA method to achieve:
The above is the detailed content of How to merge the contents of two cells into the contents of one cell. For more information, please follow other related articles on the PHP Chinese website!