☰

如何在 Excel 中将 2 个单元格的内容合并为 1 个单元格并移动剩余的列？

tag-icon • tag-icon microsoft-excel
如何在 Excel 中将 2 个单元格的内容合并为 1 个单元格并移动剩余的列？

我试图将 Excel 中同一行的单元格合并为 1 个单元格，然后将剩余的单元格向左移动。

例子：

内容 | 示例 | 更多 | 更多2

所以我想合并“内容”和“示例”，并将“更多”和“更多2”单元格向左移动，结果将是。

内容样本 | 更多 | 更多2

我找到了一个合并两列的代码，但它将两列合并为一列，但仍然占用了第二列。所以我必须“取消合并”并删除第二列，并将“更多”和“更多2”向左移动。

这是我的代码：

    Sub MergeOneCell()
    'Updateby20140128
    Dim Rng As Range
    Dim WorkRng As Range
    Dim Sigh As String
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Sigh = Application.InputBox("Symbol merge", xTitleId, ",", Type:=2)
    xOut = ""
    Application.DisplayAlerts = False
    For Each Rng In WorkRng
    xOut = xOut & Rng.Value & Sigh
    Next
    With WorkRng
    .Merge
    .Value = VBA.Left(xOut, VBA.Len(xOut) - 1)
    End With
    Application.DisplayAlerts = True
    End Sub

任何帮助都将不胜感激。我有大量数据需要处理，如果能做到这一点，我将节省大量时间。只是到目前为止我还没有找到任何解决方案。

答案1

这将接受输入并执行操作

Sub test()
Dim str As String
str = Application.InputBox("symbol", "type it", Type:=2)
Dim rng As Range
Set rng = Application.InputBox("Use mouse", "Selection", Type:=8)

For Each c In rng
c.Value = c.Value & str & c.Offset(, 1).Value
Next

rng.Offset(, 1).EntireColumn.Delete

End Sub

或者使用类似的公式=CONCATENATE(A1,"&",B1)，然后手动删除列。

答案2

我不想删除右侧的所有列，因为那里可能有其他行的值。这不像上面那样简洁，但对我来说很管用……

Sub Concat_Shift_Left()
'
' Concat_Shift_Left Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
' This macro will concatenate all cells in a selected range to the first cell,
' separating each cell's value by an input character, and will delete the remaining
' cells on only the first row, by shifting the remaining cells to the left.
' Best to be careful to only select cells on one row, unless you want all other rows loaded in too

Dim i As Integer, left_cell_addr As String, left_cell_val As String

Dim str As String
str = Application.InputBox("separator char", "type it", Type:=2) ' or = " "

Dim rng As Range
Set rng = Selection 'Application.InputBox("Use mouse", "Selection", Type:=8)

left_cell_addr = rng.Cells(1, 1).Address
left_cell_val = " " ' setup a flag for the start of the string

For Each c In rng
  If left_cell_val = " " Then
    left_cell_val = c.Value ' don't load the first char with the plug char
  Else
    left_cell_val = left_cell_val & str & c.Value
  End If
  i = i + 1
Next

Range(left_cell_addr).Value = Trim(left_cell_val)
Range(Range(left_cell_addr).Offset(0, 1).Address, Range(left_cell_addr).Offset(0, 
Application.WorksheetFunction.Min(rng.Columns.Count - 1, i - 1)).Address).Select
Selection.Delete shift:=xlToLeft

End Sub

相关内容