我有层次路径Excel 电子表格 A 列中的值:
FLEET
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM \ COMPRESSOR - DRYER
FLEET \ AIR SYSTEM \ VALVES
FLEET \ AIR SYSTEM \ RESERVOIRS
FLEET \ AIR SYSTEM \ HOSES - LINES - FITTINGS
FLEET \ BODY EXTERIOR
FLEET \ BODY EXTERIOR \ BODY PANELS
FLEET \ BODY EXTERIOR \ WIPERS
FLEET \ BODY EXTERIOR \ MIRRORS
FLEET \ BODY EXTERIOR \ ACCESSORIES
FLEET \ BODY EXTERIOR \ ACCESSORIES \ CATEGORY 1
FLEET \ BODY EXTERIOR \ ACCESSORIES \ CATEGORY 2
我想在最后一个实例中拆分每个字符串\
并保留拆分字符串的左侧。
[null]
FLEET
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM
FLEET
FLEET \ BODY EXTERIOR
FLEET \ BODY EXTERIOR
FLEET \ BODY EXTERIOR
FLEET \ BODY EXTERIOR
FLEET \ BODY EXTERIOR \ ACCESSORIES
FLEET \ BODY EXTERIOR \ ACCESSORIES
是否可以通过 Excel 2016 公式做到这一点?
答案1
=IFERROR(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1),"")
在您的评论中,您表示想要删除space
原始数据中存在的终端。您只需使用TRIM
公式中的函数:
=TRIM(IFERROR(LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2,"\",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))))-1),""))
并向下填充
如果您的 Excel 版本具有FILTERXML
和TEXTJOIN
功能,则可以使用以下命令(不会有终端space
)。
=IFERROR(TEXTJOIN("\",TRUE,FILTERXML("<t><s>" & SUBSTITUTE(A1&"\","\","</s><s>") & "</s></t>","//s[position()<last()-1]")),"")