☰

我在 Excel 列中有原始形式的联系信息。需要按行组织这些信息

tag-icon • tag-icon microsoft-excel
我在 Excel 列中有原始形式的联系信息。需要按行组织这些信息

我的源数据组织如下：

源数据

我希望它排列成这样的行：

Sr. No. Name Address Phone Email

答案1

这个答案对 OP 没有帮助，因为他们的数据并不总是以 8 行为一组。我会把它留给以后发现这个问题的人参考，做数据的行数始终相同。

看起来您的数据中有 3 行地址。我会在结果中将其保留为 3 列。

假设您的数据从单元格 A1 开始，请将此公式放入单元格 B1 中：

=IF(MOD(ROW(),8)=1,OFFSET($A1,COLUMN()-2,0),"")

将该公式复制/粘贴到范围内B1:I1，然后根据需要向下复制/粘贴多行。现在，您有了行中的数据。由于这是一次性使用，您可以复制/粘贴值以在新工作表上获取该数据，然后使用 Excel 的过滤工具删除所有空白行。

关键点：8公式中的是因为您的数据似乎是以 8 为一组的。如果情况并非总是如此，则这种解决方案将不起作用，但可以针对这种情况重新制定一般概念。

截屏

如果您的数据可靠地有 8 行并且您希望在将来粘贴新数据并让其全部自动更新，则可以将其粘贴到同一张表中的任意位置以获取过滤结果：

=LET(lastRow,XLOOKUP(FALSE,ISBLANK($A:$A),ROW($A:$A),,,-1),seq,SEQUENCE(lastRow),col,TRANSPOSE(SEQUENCE(8)),data,IF(MOD(seq,8)<>1,"",INDEX($A:$A,seq+col-1)),FILTER(data,MOD(seq,8)=1))

截屏

答案2

这也可以通过 Power Query 来实现，可在 Windows Excel 2010+ 和 Excel 365（Windows 或 Mac）中使用

使用 Power Query

  • 在数据表中选择一些单元格
  • Data => Get&Transform => from Table/Range或者from within sheet
  • 当 PQ 编辑器打开时：Home => Advanced Editor
  • 记下表格姓名在第 2 行
  • 将下面的 M 代码粘贴到您所看到的位置
  • 将第 2 行的表名改回最初生成的表名。
  • 阅读评论并探索Applied Steps以了解算法

请注意，判断什么是什么的“规则”在注释中。如果您的实际数据不一致，则需要更改规则。如果此代码不起作用，请具体说明您的实际数据与您发布的示例不一致的地方

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

//Rules:
//  Each entry starts with Serial Number CA/number/number
//     could simplify to just CA/ if that guaranteed to be non-ambiguous
//     or make it more complex if necessary
//  First row after SR will be name
//  All rows until phone number will be address
//  Phone number will always start with "MOBILE:"
//  email will always start with "email:"

//Add column so to be able to Group By ID
    #"Mark Serial Number" = Table.AddColumn(#"Changed Type", "Sr. No.", (sn)=>
        let 
            split = Text.Split(sn[Column1],"/"),
            verify = if List.Count(split) = 3
                        and split{0} = "CA"
                        and Value.Type(Number.FromText(split{1})) = type number
                        and Value.Type(Number.FromText(split{1})) = type number
                    then sn[Column1] else null
        in verify, type text),
    #"Grouper Column" = Table.FillDown(#"Mark Serial Number",{"Sr. No."}),
    #"Grouped Rows" = Table.Group(#"Grouper Column", {"Sr. No."}, {
    
        {"Name", each [Column1]{1}, type text},
    
        {"Address", (t)=>Text.Combine(
                List.Select(List.RemoveFirstN(t[Column1],2), 
                    each not 
                        (Text.StartsWith(_,"MOBILE", Comparer.OrdinalIgnoreCase)
                         or Text.StartsWith(_, "email", Comparer.OrdinalIgnoreCase))),
                    "#(lf)"), type text},
    
        {"Phone", (t)=>
        Text.Trim(
            List.Last(
                Text.Split(
                    List.Select(t[Column1], 
                        each Text.StartsWith(_,"MOBILE", Comparer.OrdinalIgnoreCase)){0},
                "MOBILE:"))),
                type text},
    
        {"Email", (t)=>Text.Trim(
            List.Last(
                Text.Split(
                    List.Select(t[Column1], 
                        each Text.StartsWith(_,"email:", Comparer.OrdinalIgnoreCase)){0},
                "email:"))),
                type text}
      })
in
    #"Grouped Rows"

原始数据

列 1
CA/2018/92890
阿布舍克·查卡
B-39/F VUETA VIHAR 德里警察局
社会部门-13，
罗希尼，德里，110085，
德里
手机：8076426938
电子邮件：[电子邮件保护]
CA/2019/110870
阿布舍克·古普塔
13-41/C，南加内什纳加，
德里, 110092, 德里
手机：8510840769
电子邮件：[电子邮件保护]
CA/2010/49509
阿布舍克·贾恩
缪斯号2578，
加利·佩帕尔·瓦尔特，
达兰普拉，德里，110006，
德里
手机：9811608229
电子邮件：anabhishek[电子邮件保护]
CA/2017/86603
阿布舍克·贾恩
59 SHUBH 飞地，
德里 PITAMPURA PUSHPANJALI，
110034，德里
手机：9999941389

结果

序号 姓名 地址 电话 电子邮件
CA/2018/92890 阿布舍克·查卡 B-39/F VUETA VIHAR 德里警察协会第 13 区，罗希尼，德里，110085，德里 8076426938 [电子邮件保护]
CA/2019/110870 阿布舍克·古普塔 13-41/C，南 GANESH NAGA，德里，110092，德里 8510840769 [电子邮件保护]
CA/2010/49509 阿布舍克·贾恩 MUSE NO.2578，GALI PEEPAL WALT，DHARAMPURA，德里，110006，德里 9811608229 阿纳比舍克[电子邮件保护]
CA/2017/86603 阿布舍克·贾恩 德里，PITAMPURA PUSHPANJALI，SHUBH ENCLAVE 59 号，邮编 110034 9999941389 [电子邮件保护]

答案3

假设：

  • 每个数据条目的最后一行以“电子邮件：”开头
  • 条目的前两行是“序号”和“姓名”
  • 条目的最后两行是“电话”和“电子邮件”
  • 前两个和后两个之间的所有内容都是地址

解决方案：

您可以使用纯公式来执行此操作，但需要几个步骤。首先，在数据上方插入一行。我用列标题填充了这一行，以帮助我跟踪我正在做的事情，但它实际上只是为了使“最后一封电子邮件”字段（C 列）中的公式更容易输入。最终结果如下所示：

截屏

字段名称 公式细胞 公式
是否为电子邮件 B2 =UPPER(LEFT(A2,6))="EMAIL:"
最后一封电子邮件 C2 =IFERROR(LOOKUP(2,1/B1:B$2,ROW(B1:B$2)),1)
序号 D2 =IF(NOT(B2),"",INDEX(A:A,C2+1))
姓名 E2 =IF(NOT(B2),"",INDEX(A:A,C2+2))
地址 F2 =IF(NOT(B2),"",SUBSTITUTE(SUBSTITUTE(TEXTJOIN(", ",TRUE,OFFSET($A$1,C2+2,0,ROW()-C2-4)),", ,",",,"),",,",","))
电话 G2 =IF(NOT(B2),"",A1)
电子邮件 氢气 =IF(NOT(B2),"",A2)

那个大地址公式用双逗号来清理，这样就变得更长了SUBSTITUTE()。你的许多地址末尾已经有一个逗号，所以我尝试通过公式来清理它。如果你手动清理这些数据（之前或之后），F2 中的公式可以是这样的：

=IF(NOT($B2),"",TEXTJOIN(", ",TRUE,OFFSET($A$1,C2+2,0,ROW()-C2-4)))

解释：

该IsEmail字段仅通过检查固定字符串来查找条目的最后一行。Last Email找到以前的TRUE通过查找中的最后一个值来判断条目的结尾IsEmail。如果没有找到，那么它一定是第一个条目，所以我们使用该值，1因为我们真的关心的是当前条目的开始和结束。

一旦我们有了这两个值，提取其他大部分内容就很容易了。地址有点棘手，因为我们必须连接一些单元格，但我们可以使用数据条目的开头和结尾之间的差异来确定地址有多少行。

一旦行是空白的或只有一个条目，就可以将值复制/粘贴到其他地方，并使用 Excel 的筛选工具删除空白。此解决方案在电子邮件地址中保留了“email:”等文本，在电话号码中保留了“Mobile:”等文本，但这些文本很容易在公式中或作为后处理的一部分删除。

相关内容