如果Excel 2010中单元格区域中的单元格包含特定文本,请将整个单元格文本移动到其他列中

atmip9wb  于 2022-12-20  发布在  其他
关注(0)|答案(2)|浏览(189)

我很难找到答案...
每个月,我都会收到一个电子表格,里面装满了客户数据,这些数据是从某种CRM软件中提取出来的,而且数据很混乱。有些单元格被合并,有些没有。当你取消合并整个表格时,你最终会得到一列数据,这些数据随机分布在3列中,并与另一列数据混合,即电子邮件地址分布在3列中,并与邮政编码混合。
我希望能够做的是搜索S、T和U列中包含“@”的单元格,并将整个电子邮件地址移动(而不是复制)到同一行的V列。
我怎样才能做到呢?

xoefb8l8

xoefb8l81#

您可以使用以下公式转换为V1来实现这一点:

=INDEX(S1:U1,MATCH(TRUE,NOT(ISERROR(SEARCH("@",S1:U1))),0))

公式需要按数组公式输入,即Ctrl-Shift-Enter。

k10s72fa

k10s72fa2#

按Alt+F11打开Visual Basic编辑器,然后单击“插入”、“模块”。将其粘贴进去。或者,只需下载示例文件here。然后在“视图/宏”下,将出现movemail()例程。运行该例程。
我接受支票,汇票,贝宝,比特币...:-)J/J享受吧。

Sub moveemail()

Dim ws As Worksheet
Dim thisCell As Range, nextCell As Range, lookAt As Range
Dim foundAt As String, lookFor As String
Dim lastRow As Long
lookFor = "@"
On Error GoTo Err

'get last populated cell
Set ws = Application.ActiveSheet
With ws
    If WorksheetFunction.CountA(Cells) > 0 Then
        lastRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    End If
End With

' go cell by cell looking for @ from row 1 to row 10000
Set lookAt = ActiveSheet.Range("S1:U" & lastRow)
Set thisCell = lookAt.Find(what:=lookFor, LookIn:=xlValues, lookAt:=xlPart, SearchDirection:=xlNext)
    If Not thisCell Is Nothing Then
        Set nextCell = thisCell
        Do
            Set thisCell = lookAt.FindNext(After:=thisCell)
            If Not thisCell Is Nothing Then
                foundAt = thisCell.Address

                            thisCell.Copy Range("V" & thisCell.Row)
                            thisCell.ClearContents
            Else
                Exit Do
            End If
            If thisCell.Address = nextCell.Address Then Exit Do
        Loop
    Else
        'MsgBox SearchString & " not Found"
        Exit Sub
    End If
Err:
    'MsgBox Err.Number
    Exit Sub
End Sub

相关问题