Excel:仅引用一部分文件名 [英] Excel: referencing a portion of file name only

问题描述

请帮助，我希望在excel单元格中引用部分文件名，请参见以下示例:

Please help, i'm looking to reference a portion of my file name in an excel cell, see below example:

文件名:000_XYZ_ABC_DEF;我需要引用ABC部分(但是，这并不限于3个字母，因为这会有所变化)-有没有我可以使用的公式来满足差异以及仅引用该部分?

File name: 000_XYZ_ABC_DEF ; I need to reference the ABC portion (but this won't be limited to only 3 letters as this varies) - is there a formula I could use that would cater for the variance as well as only referencing this portion?

谢谢

推荐答案

嗯，使用MID()和FIND()非常残酷，但是可以:

Well, very brutal with MID() and FIND() but works:

基本解释是MID()从您定义的起点和终点的字符串中提取字符.

Basic explanation is that MID() takes the characters from a string from the start points and end point you define.

find()的用途是找到第二个下划线，该下划线是起点(尽管加1)，而第三个下划线的位置与第二个下划线的位置给出要返回的字符数.

The use of find() is to locate the second underscore which is the start point (plus 1 though) and the position of the 3rd underscore which with the position of the second gives the number of characters to return.

将所有内容拆开将是一个好习惯.

It will be good practice to take it all apart.

我添加了完整的功能，以便您可以直接复制它:

I have added the complete function, so that you can copy it directly:

=MID(A1,FIND("_",A1,FIND("_",A1,1)+1)+1,FIND("_",A1,FIND("_",A1,FIND("_",A1,1)+1)+1)-FIND("_",A1,FIND("_",A1,1)+1)-1)

在注释中，函数中未包含"_"，即正在查找的文本FIND()的精确位.将您的评论与我的进行比较:

In your comment you have not included in your function the "_" which is the precise bits of the text FIND() is looking for. Compare mine to yours from your comment:

=MID(A1,FIND("",A1,FIND("",A1,1)+1)+1,FIND("",A1,FIND("",A1,FIND("",A1,1)+1)+1)-FIND("",A1,1)+1)-1

这篇关于Excel:仅引用一部分文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！