Python解析Excel中结构不一致的冒号分隔单元格数据方案

针对3行单元格存储无固定结构的冒号分隔角色文本、单单元格角色条目数不固定的解析需求，优先用Python实现，适配性远高于Excel公式。

方案1：Python + pandas 实现（推荐，适配动态条目数） #

先安装依赖库：
pip install pandas openpyxl

核心逻辑是先按角色关键词切分独立条目块，再用正则匹配固定字段，不需要提前约定每个单元格的Employee/Manager数量，单单元格有任意多条角色都能正常解析。
可直接运行的代码如下：

import pandas as pd
import re

# 替换为你的实际文件路径、工作表名
raw_df = pd.read_excel("待解析文件.xlsx", sheet_name="原始数据", header=None)
# 读取前3行的目标单元格内容，对应Cell1/Cell2/Cell3
raw_text_list = raw_df.iloc[:3, 0].dropna().astype(str).tolist()

# 字段匹配规则，可根据实际文本格式微调正则
extract_rules = {
    "角色类型": r"(Employee|Manager)",
    "Info": r"Info\s*:\s*(.*?)(?=\s+(Response|Request Date|Response Date|Employee|Manager)\s*:|$)",
    "Response": r"Response\s*:\s*(.*?)(?=\s+(Info|Request Date|Response Date|Employee|Manager)\s*:|$)",
    "Request Date": r"Request Date\s*:\s*(.*?)(?=\s+(Info|Response|Response Date|Employee|Manager)\s*:|$)",
    "Response Date": r"Response Date\s*:\s*(.*?)(?=\s+(Info|Response|Request Date|Employee|Manager)\s*:|$)"
}

parsed_result = []
for cell_num, content in enumerate(raw_text_list, start=1):
    # 规整多余空白字符，避免换行、多空格影响匹配
    clean_content = re.sub(r"\s+", " ", content.strip())
    # 定位所有角色条目的起始位置
    role_starts = [match.start() for match in re.finditer(r"(Employee|Manager)\s*:", clean_content, re.I)]
    # 切分单个角色块
    for idx in range(len(role_starts)):
        block_start = role_starts[idx]
        block_end = role_starts[idx+1] if idx < len(role_starts)-1 else len(clean_content)
        single_role_block = clean_content[block_start:block_end]
        # 逐字段提取
        row = {"来源单元格": f"Cell {cell_num}"}
        for field, pattern in extract_rules.items():
            match_res = re.search(pattern, single_role_block, re.I)
            row[field] = match_res.group(1).strip() if match_res else ""
        parsed_result.append(row)

# 导出结构化结果到Excel
output_df = pd.DataFrame(parsed_result)
output_df.to_excel("结构化解析结果.xlsx", index=False)

使用注意：

• 如果实际文本里的字段名有拼写差异、特殊符号，直接修改extract_rules里的正则匹配规则即可
• 匹配失败的字段会自动留空，不会中断运行
• 导出结果默认带来源单元格标记，方便核对原始数据

方案2：Excel公式实现（仅适合Excel 365/2021及以上版本，维护成本高） #

如果不想写代码，可尝试用动态数组公式实现，仅适合单单元格角色条目不超过2个的简单场景：

• 将3行原始文本粘贴到A1/A2/A3单元格
• 用TEXTSPLIT函数，传入{"Employee:","Manager:"}作为分隔符，将单元格内容拆分为独立的角色文本块
• 对拆分出的每个文本块，嵌套TEXTAFTER+TEXTBEFORE函数，分别提取Info、Response、Request Date、Response Date四个字段的值
• 最后用WRAPROWS+TOCOL函数将零散提取的结果拼接为标准二维表

该方案缺陷明显：低版本Excel无动态数组函数无法使用；角色条目数变多后公式嵌套层数会快速超出限制，字段规则调整需要修改所有嵌套公式，不推荐长期使用。

内容的提问来源于stack exchange，提问作者beginnerman