Excel查找最后两个下划线之间的文本

Question

我需要一个 Excel 公式来查找 Excel 单元格中最后两个下划线之间的文本:

即

AAA_BBB_CCC_DDDD_E => DDDD<br/>
AAA_BBB_CCC_DDD_EEE_E => EEE<br/>
AAA_BBB_CCC_DDD_EEE_F_ => F<br/>
AAA_BBB_CCC_DDD_EE_GGGGGGGGGG => EE<br/>
AAA_BBB_CCC_DDD_EEE_FFF_GGGGGGGGGGGG_H => GGGGGGGGGGGG

我得到的公式基于以下的最后一个索引: EXCEL LAST INDEX OF

=RIGHT(LEFT(A2,SEARCH("@",SUBSTITUTE(A2,"_","@",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))))-1),LEN(LEFT(A2,SEARCH("@",SUBSTITUTE(A2,"_","@",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))))-1))-SEARCH("@",SUBSTITUTE(LEFT(A2,SEARCH("@",SUBSTITUTE(A2,"_","@",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))))-1),"_","@",LEN(LEFT(A2,SEARCH("@",SUBSTITUTE(A2,"_","@",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))))-1))-LEN(SUBSTITUTE(LEFT(A2,SEARCH("@",SUBSTITUTE(A2,"_","@",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))))-1),"_","")))))

我需要让这个变得不那么复杂，有什么想法吗？

Answer 1

试试这个

=TRIM(LEFT(RIGHT(SUBSTITUTE(H3,"_",REPT(" ",100)),200),100))

请注意，如果任何段可能超过 50 个字符，请相应增加 100 和 200 个数字