blocks|key|4538097|text|我对excel不是很在行，但我找到了这些here|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4538098|=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))%2B1,256)|code-block|syntax|javascript|4538099|A1可以是工作表中任何无错误的单元格。|style|CODE|4538100|有关工作表的完整路径和名称，请使用|4538101|=CELL("filename",A1)|4538102|entityMap|0|LINK|mutability|MUTABLE|url|http://www.ozgrid.com/VBA/return-sheet-name.htm^0|K|4|0|0|0|0|2|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|13|8|@$A|14|B|15|K|L]]|9|@]|C|$]]|$1|M|3|N|5|6|7|16|8|@]|9|@]|C|$]]|$1|O|3|P|5|F|7|17|8|@]|9|@]|C|$G|H]]|$1|Q|3|-4|5|6|7|18|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

Not very good with excel, but I found these <a href="http://www.ozgrid.com/VBA/return-sheet-name.htm" rel="noreferrer">here</a> 

<pre><code>=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
</code></pre>

and <code>A1</code> can be any non-error cell in the sheet. 
For the full path and name of the sheet, use

<pre><code>=CELL("filename",A1)
</code></pre>

blocks|key|4538225|text|对于Excel的最新版本，公式语法为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4538226|=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))%2B1;255)|code-block|syntax|javascript|4538227|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For recent versions of Excel, the formula syntax is: 

<pre><code>=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255)
</code></pre>

blocks|key|4538134|text|下面将隔离工作表名称：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4538135|=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))|code-block|syntax|javascript|4538136|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The below will isolate the sheet name:

<pre><code>=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
</code></pre>

blocks|key|4538264|text|其他答案中给出的公式都不支持filepath中有字符]的情况。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4538265|下面的公式比较复杂，但它可以很好地处理这种情况：|4538266|+=MID(CELL("filename",A1),FIND("]",CELL("filename",A1),FIND("?",SUBSTITUTE(CELL("filename",A1),"\","?",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\","")))))%2B1,LEN(CELL("filename",A1)))|code-block|syntax|javascript|4538267|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

None of the formulas given in other answers supports the case if there is character ] in filepath.

The formula below is more complex, but it can handle such cases properly:

<pre><code> =MID(CELL("filename",A1),FIND("]",CELL("filename",A1),FIND("?",SUBSTITUTE(CELL("filename",A1),"\","?",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\","")))))+1,LEN(CELL("filename",A1)))
</code></pre>

blocks|key|30695|text|我已经打开了一个模块，所以我创建了一个自定义函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|30696|Public+Function+Sheetname+(ByRef+acell+as+Range)+as+string
Sheetname+=+acell.Parent.Name+
End+Function+|code-block|syntax|javascript|30697|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I had a module already open so I made a custom function: 

<pre><code>Public Function Sheetname (ByRef acell as Range) as string
Sheetname = acell.Parent.Name 
End Function 
</code></pre>

blocks|key|30789|text|下面的方法适用于我，并且比其他解决方案更简单(至少对我而言)，同时仍然处理文件名中的方括号：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|30790|=MID(CELL("filename",+A1),6%2BSEARCH(".xlsx]",CELL("filename",+A1)),32)|code-block|syntax|javascript|30791|这假设它是一个"+xlsx“文件，并且除了xlsx后缀之外，文件名不包含".xlsx]”，在我的例子中，这是一个足够安全的假设。|30792|如果你想同时处理".xlsx“和".xls”文件名，你可以使用：|30793|=MID(SUBSTITUTE(CELL("filename",+A1),".xls]",".xlsx]"),6%2BSEARCH(".xlsx]",SUBSTITUTE(CELL("filename",+A1),".xls]",".xlsx]")),32)|30794|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

The below works for me and is simpler (to me at least) than other solutions, while still handling a square bracket in the file name:
<pre><code>=MID(CELL(&quot;filename&quot;, A1),6+SEARCH(&quot;.xlsx]&quot;,CELL(&quot;filename&quot;, A1)),32)
</code></pre>
This assumes it is an &quot;xlsx&quot; file and that the filename will not contain &quot;.xlsx]&quot; in addition to the xlsx suffix, which in my case is an assumption that is safe enough to make.
If you wanted to handle both &quot;.xlsx&quot; and &quot;.xls&quot; file names, you could use:
<pre><code>=MID(SUBSTITUTE(CELL(&quot;filename&quot;, A1),&quot;.xls]&quot;,&quot;.xlsx]&quot;),6+SEARCH(&quot;.xlsx]&quot;,SUBSTITUTE(CELL(&quot;filename&quot;, A1),&quot;.xls]&quot;,&quot;.xlsx]&quot;)),32)
</code></pre>

blocks|key|4538165|text|我很确定你可以谷歌一下这个。我刚刚做了，这是我想到的第一件事。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4538166|In+Excel+it+is+possible+to+use+the+CELL+function/formula+and+the+MID+and+FIND+to+return+the+name+of+an+Excel+Worksheet+in+a+Workbook.+The+formula+below+shows+us+how;

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))%2B1,256)

Where+A1+is+any+non+error+cell+on+the+Worksheet.+If+you+want+the+full+path+of+the+Excel+Workbook,+simply+use;

=CELL("filename",A1)|code-block|syntax|javascript|4538167|唯一的问题是你必须保存这个文件才能正常工作！|4538168|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I'm pretty sure you could have Googled this. I just did, and here is the very first thing that came up for me.

<pre><code>In Excel it is possible to use the CELL function/formula and the MID and FIND to return the name of an Excel Worksheet in a Workbook. The formula below shows us how;

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

Where A1 is any non error cell on the Worksheet. If you want the full path of the Excel Workbook, simply use;

=CELL("filename",A1)
</code></pre>

The only catch is that you have to save the file for this to work!

I have searched the excel function documentation and general MSDN search but have been unable to find a way to return the sheet name without VBA.

Is there a way to get the sheet name in an excel formula without needing to resort to VBA?

What Excel formula returns the sheet name?

我已经搜索了excel函数文档和一般的MSDN搜索，但一直无法找到一种方法来返回没有VBA的工作表名称。有没有一种方法可以在excel公式中获得工作表名称，而不需要求助于VBA？

问哪个Excel公式返回工作表名称？
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问哪个Excel公式返回工作表名称？EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问哪个Excel公式返回工作表名称？
EN