Excel 2016提取含_/-字符至另一列并截断|后内容的方案咨询

需求回顾 #

• 对col1列中包含|的行，删除|及其之后的所有内容
• 从处理后的col1内容中，提取所有包含_或-的字符段；若这些段之间存在空格则合并，结果写入col2

输入数据示例 #

col1                                                  col2
Disconnected on B_NKLA_TUV_0022 As Per 
Roll DCN-BVLR-OCR-NKLT | Flapping
VAAP CLR_ NLTK_BKC_ASSO CBR Thing
VCR-DCW- NKL-NKJ-MLP | CBR-XDL-BHG
Disconnected on B_NFA_SUV_0025 As 

预期输出示例 #

col1                                                  col2
Disconnected on B_NKLA_TUV_0022 As Per             B_NKLA_TUV_0022
Roll DCN-BVLR-OCR-NKLT | Flapping                  DCN-BVLR-OCR-NKLT
VAAP CLR_ NLTK_BKC_ASSO CBR Thing                  CLR_ NLTK_BKC_ASSO
VCR-DCW- NKL-NKJ-MLP | CBR-XDL-BHG                 VCR-DCW- NKL-NKJ-MLP
Disconnected on B_NFA_SUV_0025 As                  B_NFA_SUV_0025

分步实现（Excel 2016兼容） #

步骤1：清理col1中的|及后续内容 #

新增辅助列（命名为col1_clean），输入以下公式后下拉填充：

=IFERROR(LEFT(A2,FIND("|",A2)-1),A2)

• 逻辑：若当前行col1包含|，提取|之前的所有内容；若不包含，则保留原内容。

步骤2：提取并合并目标字符段到col2 #

在col2对应单元格输入以下数组公式（输入后按Ctrl+Shift+Enter确认，而非单独回车），然后下拉填充：

=TEXTJOIN(" ",TRUE,IF(ISNUMBER(SEARCH({"_","-"},TRIM(MID(SUBSTITUTE(col1_clean," ",REPT(" ",99)),(ROW(INDIRECT("1:"&LEN(col1_clean)-LEN(SUBSTITUTE(col1_clean," ",""))+1))-1)*99+1,99)))),TRIM(MID(SUBSTITUTE(col1_clean," ",REPT(" ",99)),(ROW(INDIRECT("1:"&LEN(col1_clean)-LEN(SUBSTITUTE(col1_clean," ",""))+1))-1)*99+1,99)),""))

• 逻辑：
  1. 将col1_clean内容按空格拆分为单个片段
  2. 筛选出包含_或-的片段
  3. 用TEXTJOIN将符合条件的片段用空格合并，得到最终结果

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