How to merge the contents of two cells into the contents of one cell

How to merge the contents of two cells into 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!

How to use VBA in EXCEL to merge data from several cells into one cell and delete it

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

EXCEL vba realizes automatic merging of cells

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

How to merge the contents of multiple cells in Excel into one cell

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!