blocks|key|2784745|text|我想我明白你的意思了。例如，假设您想要以下字符串中最右边的\(存储在单元格A1中)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2784746|Drive:\Folder\SubFolder\Filename.ext|2784747|要获得最后一个\的位置，可以使用以下公式：|2784748|=FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))|code-block|syntax|javascript|2784749|这告诉我们最右边的\在字符24处。它通过查找"@“并将最后一个"\”替换为"@“来做到这一点。它使用下面的方法确定最后一个|2784750|(len(string)-len(substitute(string,+substring,+"")))\len(substring)|2784751|在此场景中，子字符串仅为"\“，长度为1，因此您可以省略末尾的分隔符，只需使用：|2784752|=FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))|2784753|现在我们可以使用它来获取文件夹路径：|2784754|=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))|2784755|下面是不带尾部的文件夹路径\|2784756|=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1)|2784757|只获取文件名：|2784758|=MID(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))%2B1,LEN(A1))|2784759|但是，这里是获取特定角色的最后一个实例右侧的所有内容的另一个版本。因此，使用我们的相同示例，这也将返回文件名：|2784760|=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT("+",LEN(A1))),LEN(A1)))|2784761|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1C|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|1D|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1E|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|1F|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|1H|8|@]|9|@]|A|$I|J]]|$1|S|3|T|5|6|7|1I|8|@]|9|@]|A|$]]|$1|U|3|V|5|H|7|1J|8|@]|9|@]|A|$I|J]]|$1|W|3|X|5|6|7|1K|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|H|7|1L|8|@]|9|@]|A|$I|J]]|$1|10|3|11|5|6|7|1M|8|@]|9|@]|A|$]]|$1|12|3|13|5|H|7|1N|8|@]|9|@]|A|$I|J]]|$1|14|3|15|5|6|7|1O|8|@]|9|@]|A|$]]|$1|16|3|17|5|H|7|1P|8|@]|9|@]|A|$I|J]]|$1|18|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|19|$]]

I think I get what you mean. Let's say for example you want the right-most \ in the following string (which is stored in cell A1):

Drive:\Folder\SubFolder\Filename.ext

To get the position of the last \, you would use this formula:

<pre><code>=FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))
</code></pre>

That tells us the right-most \ is at character 24. It does this by looking for "@" and substituting the very last "\" with an "@". It determines the last one by using 

<pre><code>(len(string)-len(substitute(string, substring, "")))\len(substring)
</code></pre>

In this scenario, the substring is simply "\" which has a length of 1, so you could leave off the division at the end and just use:

<pre><code>=FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))
</code></pre>

Now we can use that to get the folder path:

<pre><code>=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
</code></pre>

Here's the folder path without the trailing \

<pre><code>=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1)
</code></pre>

And to get just the filename:

<pre><code>=MID(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))
</code></pre>

However, here is an alternate version of getting everything to the right of the last instance of a specific character. So using our same example, this would also return the file name:

<pre><code>=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))
</code></pre>

blocks|key|179291|text|您可以使用我创建的这个函数在字符串中查找字符串的最后一个实例。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|179292|当然，公认的Excel公式是有效的，但它太难阅读和使用了。在某些情况下，您必须将其分成更小的块，以便它是可维护的。我下面的函数是可读的，但这是无关紧要的，因为您可以使用命名参数在公式中调用它。这使得它的使用变得简单。|179293|Public+Function+FindLastCharOccurence(fromText+As+String,+searchChar+As+String)+As+Integer
Dim+lastOccur+As+Integer
lastOccur+=+-1
Dim+i+As+Integer
i+=+0
For+i+=+Len(fromText)+To+1+Step+-1
++++If+Mid(fromText,+i,+1)+=+searchChar+Then
++++++++lastOccur+=+i
++++++++Exit+For
++++End+If
Next+i

FindLastCharOccurence+=+lastOccur
End+Function|code-block|syntax|javascript|179294|我是这样使用它的：|179295|=RIGHT(A2,+LEN(A2)+-+FindLastCharOccurence(A2,+"\"))|179296|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You could use this function I created to find the last instance of a string within a string.

Sure the accepted Excel formula works, but it's much too difficult to read and use. At some point you have to break out into smaller chunks so it's maintainable. My function below is readable, but that's irrelevant because you call it in a formula using named parameters. This makes using it simple.

<pre><code>Public Function FindLastCharOccurence(fromText As String, searchChar As String) As Integer
Dim lastOccur As Integer
lastOccur = -1
Dim i As Integer
i = 0
For i = Len(fromText) To 1 Step -1
 If Mid(fromText, i, 1) = searchChar Then
 lastOccur = i
 Exit For
 End If
Next i

FindLastCharOccurence = lastOccur
End Function
</code></pre>

I use it like this:

<pre><code>=RIGHT(A2, LEN(A2) - FindLastCharOccurence(A2, "\"))
</code></pre>

blocks|key|2784795|text|创建一个自定义函数并在您的公式中使用它如何？VBA有一个内置的函数InStrRev，它可以做你想要的事情。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2784796|将其放入一个新模块中：|2784797|Function+RSearch(str+As+String,+find+As+String)
++++RSearch+=+InStrRev(str,+find)
End+Function|code-block|syntax|javascript|2784798|您的函数将如下所示(假设原始字符串为B1格式)：|2784799|=LEFT(B1,RSearch(B1,"\"))|2784800|entityMap^0|X|8|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|W|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|X|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|R|$]]

How about creating a custom function and using that in your formula? VBA has a built-in function, <code>InStrRev</code>, that does exactly what you're looking for.

Put this in a new module:

<pre><code>Function RSearch(str As String, find As String)
 RSearch = InStrRev(str, find)
End Function
</code></pre>

And your function will look like this (assuming the original string is in B1):

<pre><code>=LEFT(B1,RSearch(B1,"\"))
</code></pre>

blocks|key|2997297|text|在VBA中执行此操作的简单方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2997298|YourText+=+"c:\excel\text.txt"
xString+=+Mid(YourText,+2+%2B+Len(YourText)+-+InStr(StrReverse(YourText),+"\"+))|code-block|syntax|javascript|2997299|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|$]]

A simple way to do that in VBA is:

<pre><code>YourText = "c:\excel\text.txt"
xString = Mid(YourText, 2 + Len(YourText) - InStr(StrReverse(YourText), "\" ))
</code></pre>

blocks|key|179347|text|tigeravatar和Jean-François+Corbett建议使用此公式来生成"\“字符最后一次出现的字符串右侧|type|unstyled|depth|inlineStyleRanges|entityRanges|data|179348|=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT("+",LEN(A1))),LEN(A1)))|code-block|syntax|javascript|179349|如果用作分隔符的字符是空格“"，则必须将公式更改为：|179350|=SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"+",REPT("{",LEN(A1))),LEN(A1)),"{","")|179351|无需提及，"{“字符可以替换为任何不会”正常“出现在要处理的文本中的字符。|179352|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|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

tigeravatar and Jean-François Corbett suggested to use this formula to generate the string right of the last occurrence of the "\" character

<pre><code>=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))
</code></pre>

If the character used as separator is space, " ", then the formula has to be changed to:

<pre><code>=SUBSTITUTE(RIGHT(SUBSTITUTE(A1," ",REPT("{",LEN(A1))),LEN(A1)),"{","")
</code></pre>

No need to mention, the "{" character can be replaced with any character that would not "normally" occur in the text to process.

blocks|key|2784874|text|我来派对有点晚了，但也许这能帮上忙。问题中的链接具有类似的公式，但我的公式使用IF()语句来消除错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2784875|如果你不怕Ctrl%2BShift%2BEnter，你可以用一个数组公式做得很好。|2784876|字符串(在单元格A1中)："one.two.three.four“|2784877|公式：|2784878|{=MAX(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)))}++use+Ctrl%2BShift%2BEnter|code-block|syntax|javascript|2784879|结果:+14|2784880|第一,|2784881|ROW($1:$99)|2784882|返回从1到99的整数数组：{1,2,3,4,...,98,99}。|offset|length|style|CODE|2784883|接下来，|2784884|MID(A1,ROW($1:$99),1)|2784885|返回在目标字符串中找到的长度为1的字符串数组，然后在达到目标字符串的长度后返回空字符串：{"o","n","e",".",..."u","r","","",""...}|2784886|2784887|IF(MID(I16,ROW($1:$99),1)=".",ROW($1:$99))|2784888|将数组中的每一项与字符串“”进行比较。并返回字符串中字符的索引或FALSE：{FALSE,FALSE,FALSE,4,FALSE,FALSE,FALSE,8,FALSE,FALSE,FALSE,FALSE,FALSE,14,FALSE,FALSE.....}|2784889|最后的,|2784890|=MAX(IF(MID(I16,ROW($1:$99),1)=".",ROW($1:$99)))|2784891|返回数组的最大值：14|2784892|这个公式的优点是它很短，相对容易理解，不需要任何唯一的字符。|2784893|缺点是需要使用Ctrl%2BShift%2BEnter和对字符串长度的限制。这可以通过如下所示的变体来解决，但该变体使用了OFFSET()函数，该函数是一个易失性(read:+slow)函数。|2784894|不确定这个公式相对于其他公式的速度是多少。|2784895|变体：|2784896|=MAX((MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)=".")*ROW(OFFSET($A$1,,,LEN(A1))))+works+the+same+way,+but+you+don't+have+to+worry+about+the+length+of+the+string

=SMALL(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)),2)+determines+the+2nd+occurrence+of+the+match

=LARGE(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)),2)+determines+the+2nd-to-last+occurrence+of+the+match

=MAX(IF(MID(I16,ROW($1:$99),2)=".t",ROW($1:$99)))+matches+a+2-character+string+**Make+sure+you+change+the+last+argument+of+the+MID()+function+to+the+number+of+characters+in+the+string+you+wish+to+match!|2784897|entityMap^0|0|0|0|0|0|0|0|0|D|J|0|0|0|18|14|0|0|0|12|2J|0|0|0|9|2|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1U|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|1V|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1W|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1X|8|@]|9|@]|A|$]]|$1|Q|3|R|5|J|7|1Y|8|@]|9|@]|A|$K|L]]|$1|S|3|T|5|6|7|1Z|8|@$U|20|V|21|W|X]]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|22|8|@]|9|@]|A|$]]|$1|10|3|11|5|J|7|23|8|@]|9|@]|A|$K|L]]|$1|12|3|13|5|6|7|24|8|@$U|25|V|26|W|X]]|9|@]|A|$]]|$1|14|3|Z|5|6|7|27|8|@]|9|@]|A|$]]|$1|15|3|16|5|J|7|28|8|@]|9|@]|A|$K|L]]|$1|17|3|18|5|6|7|29|8|@$U|2A|V|2B|W|X]]|9|@]|A|$]]|$1|19|3|1A|5|6|7|2C|8|@]|9|@]|A|$]]|$1|1B|3|1C|5|J|7|2D|8|@]|9|@]|A|$K|L]]|$1|1D|3|1E|5|6|7|2E|8|@$U|2F|V|2G|W|X]]|9|@]|A|$]]|$1|1F|3|1G|5|6|7|2H|8|@]|9|@]|A|$]]|$1|1H|3|1I|5|6|7|2I|8|@]|9|@]|A|$]]|$1|1J|3|1K|5|6|7|2J|8|@]|9|@]|A|$]]|$1|1L|3|1M|5|6|7|2K|8|@]|9|@]|A|$]]|$1|1N|3|1O|5|J|7|2L|8|@]|9|@]|A|$K|L]]|$1|1P|3|-4|5|6|7|2M|8|@]|9|@]|A|$]]]|1Q|$]]

I'm a little late to the party, but maybe this could help. The link in the question had a similar formula, but mine uses the IF() statement to get rid of errors.

If you're not afraid of Ctrl+Shift+Enter, you can do pretty well with an array formula.

String (in cell A1):
"one.two.three.four"

Formula:

<pre><code>{=MAX(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)))} use Ctrl+Shift+Enter
</code></pre>

Result: 14

First,

<pre><code>ROW($1:$99)
</code></pre>

returns an array of integers from 1 to 99: <code>{1,2,3,4,...,98,99}</code>. 

Next,

<pre><code>MID(A1,ROW($1:$99),1)
</code></pre>

returns an array of 1-length strings found in the target string, then returns blank strings after the length of the target string is reached: <code>{"o","n","e",".",..."u","r","","",""...}</code>

Next,

<pre><code>IF(MID(I16,ROW($1:$99),1)=".",ROW($1:$99))
</code></pre>

compares each item in the array to the string "." and returns either the index of the character in the string or FALSE: <code>{FALSE,FALSE,FALSE,4,FALSE,FALSE,FALSE,8,FALSE,FALSE,FALSE,FALSE,FALSE,14,FALSE,FALSE.....}</code>

Last, 

<pre><code>=MAX(IF(MID(I16,ROW($1:$99),1)=".",ROW($1:$99)))
</code></pre>

returns the maximum value of the array: <code>14</code>

Advantages of this formula is that it is short, relatively easy to understand, and doesn't require any unique characters.

Disadvantages are the required use of Ctrl+Shift+Enter and the limitation on string length. This can be worked around with a variation shown below, but that variation uses the OFFSET() function which is a volatile (read: slow) function. 

Not sure what the speed of this formula is vs. others. 

Variations:

<pre><code>=MAX((MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)=".")*ROW(OFFSET($A$1,,,LEN(A1)))) works the same way, but you don't have to worry about the length of the string

=SMALL(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)),2) determines the 2nd occurrence of the match

=LARGE(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)),2) determines the 2nd-to-last occurrence of the match

=MAX(IF(MID(I16,ROW($1:$99),2)=".t",ROW($1:$99))) matches a 2-character string **Make sure you change the last argument of the MID() function to the number of characters in the string you wish to match!
</code></pre>

blocks|key|2997376|text|刚刚想出了这个解决方案，不需要VBA；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2997377|在我的例子中找到"_“的最后一次出现；|2997378|=IFERROR(FIND(CHAR(1);SUBSTITUTE(A1;"_";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"_";"")));0)|code-block|syntax|javascript|2997379|从里到外解释；|2997380|SUBSTITUTE(A1;"_";"")+=>+replace+"_"+by+spaces
LEN(+*above*+)+=>+count+the+chars
LEN(A1)-+*above*++=>+indicates+amount+of+chars+replaced+(=+occurrences+of+"_")
SUBSTITUTE(A1;"_";CHAR(1);+*above*+)+=>+replace+the+Nth+occurence+of+"_"+by+CHAR(1)+(Nth+=+amount+of+chars+replaced+=+the+last+one)
FIND(CHAR(1);+*above*+)+=>+Find+the+CHAR(1),+being+the+last+(replaced)+occurance+of+"_"+in+our+case
IFERROR(+*above*+;"0")+=>+in+case+no+chars+were+found,+return+"0"|2997381|希望这能对你有所帮助。|2997382|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Just came up with this solution, no VBA needed;

Find the last occurance of "_" in my example;

<pre><code>=IFERROR(FIND(CHAR(1);SUBSTITUTE(A1;"_";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"_";"")));0)
</code></pre>

Explained inside out; 

<pre><code>SUBSTITUTE(A1;"_";"") =&gt; replace "_" by spaces
LEN( *above* ) =&gt; count the chars
LEN(A1)- *above* =&gt; indicates amount of chars replaced (= occurrences of "_")
SUBSTITUTE(A1;"_";CHAR(1); *above* ) =&gt; replace the Nth occurence of "_" by CHAR(1) (Nth = amount of chars replaced = the last one)
FIND(CHAR(1); *above* ) =&gt; Find the CHAR(1), being the last (replaced) occurance of "_" in our case
IFERROR( *above* ;"0") =&gt; in case no chars were found, return "0"
</code></pre>

Hope this was helpful.

blocks|key|2784953|text|考虑到@SSilk所做的评论的一部分，我的最终目标实际上是把所有东西都放在最后一个出现的地方。另一个非常简单的公式是复制一列(比如A)字符串列，然后在复制(比如ColumnB)时应用查找和替换。举个例子：Drive:\Folder\SubFolder\Filename.ext|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2784954|​|2784955|📷|atomic|2784956|2784957|这将返回所选字符的最后一个实例(这里是Filename.ext)之后的剩余内容(这里是\)，这有时是目标，并有助于使用简短的公式找到最后一个这样的字符的位置，例如：|2784958|=FIND(B1,A1)-1|code-block|syntax|javascript|2784959|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string#comment38712659_18617720|1|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/9e92e6d42f110934df2785afd5ccf067.jpeg|imageAlt^0|1T|1|2U|10|3|6|0|0|0|0|1|1|0|0|J|C|17|1|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@$9|17|A|18|B|C]|$9|19|A|1A|B|C]]|D|@$9|1B|A|1C|1|1D]]|E|$]]|$1|F|3|G|5|6|7|1E|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1F|8|@]|D|@$9|1G|A|1H|1|1I]]|E|$]]|$1|K|3|G|5|6|7|1J|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|1K|8|@$9|1L|A|1M|B|C]|$9|1N|A|1O|B|C]]|D|@]|E|$]]|$1|N|3|O|5|P|7|1P|8|@]|D|@]|E|$Q|R]]|$1|S|3|-4|5|6|7|1Q|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]|10|$5|11|W|12|E|$13|14|15|-4]]]]

Considering a part of a Comment made by <a href="https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string#comment38712659_18617720">@SSilk</a> my end goal has really been to get everything to the right of that last occurence an alternative approach with a very simple formula is to copy a column (say <code>A</code>) of strings and on the copy (say ColumnB) apply Find and Replace. For instance taking the example: <code>Drive:\Folder\SubFolder\Filename.ext</code>

<a href="https://i.stack.imgur.com/Nf9M0.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Nf9M0.jpg" alt="Find what"></a>

This returns what remains (here <code>Filename.ext</code>) after the last instance of whatever character is chosen (here <code>\</code>) which is sometimes the objective anyway and facilitates finding the position of the last such character with a short formula such as:

<pre><code>=FIND(B1,A1)-1
</code></pre>

blocks|key|2784983|text|来得太晚了，但一个简单的解决方案是使用VBA创建自定义函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2784984|将函数添加到WorkBook、工作表或VBA模块中的VBA|2784985|Function+LastSegment(S,+C)
++++LastSegment+=+Right(S,+Len(S)+-+InStrRev(S,+C))
End+Function|code-block|syntax|javascript|2784986|然后单元格公式|2784987|=lastsegment(B1,"/")|2784988|，并且要在单元格B1中搜索的字符串将使用单元格B1中最后一个"/“结尾的文本填充该单元格。没有长度限制，没有晦涩的公式。我认为唯一的缺点是需要一个支持宏的工作簿。|2784989|可以通过这种方式调用任何用户VBA函数，以将值返回到单元公式，包括作为内置Excel函数的参数。|2784990|如果要大量使用该函数，则需要检查字符不在字符串中、字符串为空等情况。|2784991|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Very late to the party, but A simple solution is using VBA to create a custom function.

Add the function to VBA in the WorkBook, Worksheet, or a VBA Module

<pre><code>Function LastSegment(S, C)
 LastSegment = Right(S, Len(S) - InStrRev(S, C))
End Function
</code></pre>

Then the cell formula

<pre><code>=lastsegment(B1,"/")
</code></pre>

in a cell and the string to be searched in cell B1 will populate the cell with the text trailing the last "/" from cell B1.
No length limit, no obscure formulas.
Only downside I can think is the need for a macro-enabled workbook.

Any user VBA Function can be called this way to return a value to a cell formula, including as a parameter to a builtin Excel function. 

If you are going to use the function heavily you'll want to check for the case when the character is not in the string, then string is blank, etc.

blocks|key|2785002|text|在较新版本的Excel+(2013及以上)中，快速填充可能是一种简单快捷的解决方案，请参阅：Using+Flash+Fill+in+Excel+。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2785003|entityMap|0|LINK|mutability|MUTABLE|url|https://support.office.com/en-us/article/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7^0|1A|Q|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

In newer versions of Excel (2013 and up) flash fill might be a simple and quick solution see: <a href="https://support.office.com/en-us/article/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7" rel="nofollow noreferrer">Using Flash Fill in Excel
</a>.

blocks|key|175567|text|单元格A1+=+find/the/position/of/the+last+slash|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|175568|简单的方法是颠倒文本，然后像往常一样找到第一个斜杠。现在你可以得到全文的长度减去这个数字。|175569|如下所示：|175570|=LEN(A1)-FIND("/",REVERSETEXT(A1),1)%2B1|175571|这将返回21，最后一个/的位置|175572|entityMap^0|3|14|0|0|0|0|12|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|T|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|L|3|M|5|6|7|X|8|@]|D|@]|E|$]]|$1|N|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|O|$]]

Cell <code>A1 = find/the/position/of/the last slash</code>

simple way to do it is reverse the text and then find the first slash as normal. Now you can get the length of the full text minus this number.

Like so:

<code>=LEN(A1)-FIND("/",REVERSETEXT(A1),1)+1</code>

This returns 21, the position of the last /

blocks|key|175581|text|如果只查找字符"~“的最后一个实例的位置，则=len(substitute(String，"~"，""))%2B1|type|unstyled|depth|inlineStyleRanges|entityRanges|data|175582|我确信有一个版本可以处理字符串的最后一个实例，但我必须继续工作。|175583|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

If you're only looking for the position of the last instance of character &quot;~&quot; then
=len(substitute(String,&quot;~&quot;,&quot;&quot;))+1
I'm sure there is version that will work with the last instance of a string but I have to get back to work.

blocks|key|176896|text|对于A1中的字符串和B1中的子字符串，使用：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|176897|=XMATCH(B1,MID(A1,SEQUENCE(LEN(A1)),LEN(B1)),,-1)|176898|从里到外，MID(A1,SEQUENCE(LEN(A1)),LEN(B1))将字符串A1拆分成一个动态的子字符串数组，每个子字符串的长度为B1。为了找到上次出现的子串B1的位置，我们使用XMATCH，并将其Search_mode参数设置为-1。|176899|entityMap^0|2|2|A|2|0|0|1D|0|5|X|16|2|1X|2|2B|2|2L|6|2V|B|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]|$9|10|A|11|B|C]|$9|12|A|13|B|C]|$9|14|A|15|B|C]]|D|@]|E|$]]|$1|J|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|K|$]]

For a string in <code>A1</code> and substring in <code>B1</code>, use:
<code>=XMATCH(B1,MID(A1,SEQUENCE(LEN(A1)),LEN(B1)),,-1)</code>
Working from inside out, <code>MID(A1,SEQUENCE(LEN(A1)),LEN(B1))</code> splits string <code>A1</code> into a dynamic array of substrings, each the length of <code>B1</code>. To find the position of the last occurrence of substring <code>B1</code>, we use <code>XMATCH</code> with its <code>Search_mode</code> argument set to -1.

Is there an efficient way to identify the last character/string match in a string using base functions? I.e. not the last character/string of the string, but the position of a character/string's last occurrence in a string. <code>Search</code> and <code>find</code> both work left-to-right so I can't think how to apply without lengthy recursive algorithm. And <a href="http://www.mrexcel.com/archive/General/30895.html">this solution</a> now seems obsolete.

Excel: last character/string match in a string

是否有一种有效的方法来使用基函数来识别字符串中的最后一个字符/字符串匹配？即，不是字符串的最后一个字符/字符串，而是字符/字符串的最后一个出现位置( in a string )。Search和find都是从左到右工作的，所以如果没有冗长的递归算法，我不知道该如何应用。而现在似乎已经过时了。

问Excel:字符串中的最后一个字符/字符串匹配
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel:字符串中的最后一个字符/字符串匹配EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel:字符串中的最后一个字符/字符串匹配
EN