我想使用 Excel 将几行合并为一行。我已经使用 CONCATENATE 函数完成了此操作,但这次我需要自动化该过程,因为我在文件中有几个条目。我从思科 CME 中提取了电话 IP 电话信息,其中每个电话信息都在一行上,如下所示:
ephone-1[0] Mac: TCP socket:[57] activeLine:0 whisperLine:0 REGISTERED in SCCP ver 20/17 max_streams=1
mediaActive:0 whisper_mediaActive:0 startMedia:0 offhook:0 ringing:0 reset:0 reset_sent:0 paging 0 debug:0 caps:8
IP:---------- * 35419 6941 keepalive 54113 max_line 4 available_line 4
button 1: cw:1 ccw:(0 0)
dn 1 number ------- CH1 IDLE CH2 IDLE shared
Preferred Codec: g711ulaw
Lpcor Type: none Username: ---- Password: ------
ephone-2[1] Mac:-------- TCP socket:[77] activeLine:0 whisperLine:0 REGISTERED in SCCP ver 20/17 max_streams=1
mediaActive:0 whisper_mediaActive:0 startMedia:0 offhook:0 ringing:0 reset:0 reset_sent:0 paging 0 debug:0 caps:8
IP:------- * 35189 6941 keepalive 117528 max_line 4 available_line 4
button 1: cw:1 ccw:(0 0)
dn 2 number ------ CH1 IDLE CH2 IDLE shared
Preferred Codec: g711ulaw
Lpcor Type: none
每个电子邮件由文件上的一行或两个空行分隔。大约有 350 个条目,我想自动化该过程。这个过程应该类似于将 ephone 的每一行合并为一行,这样最后我将有 350 行包含 350 个 ephone 的信息。
有谁知道如何在Excel上做到这一点?我真的很感激你的帮助。
此致敬意
这段代码应该这样做,合并,并删除多余的行
Sub ConsolidateRows_NoMatch()
'takes rows and consolidate one or many cells, based on one or many cells matching with above or below rows.
Dim lastRow As Long, i As Long, j As Long
Dim colMatch As Variant, colConcat As Variant
'turn off updates to speed up code execution
With application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
lastRow = range("A" & Rows.Count).End(xlUp).Row 'get last row
For i = lastRow To 1 Step -1 'loop from last Row to one
If Len(Cells(i, 1)) > 0 Then
If Left(Cells(i, 1), 6) <> "ephone" Then
Cells(i - 1, 1) = Cells(i - 1, 1) & Cells(i, 1)
Else
GoTo nxti:
End If
End If
Rows(i).Delete
nxti:
Next
With application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub