vba 在 Excel 中第 n 次出现字符后提取文本字符串（函数或 VB）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26483135/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extracting text string after nth occurrence of a character in Excel (functions or VB)
提问by GaryWellock
Looking for VB or Excel function to return
寻找 VB 或 Excel 函数返回
azat-tab-25mg
azat-tab-25mg
from
从
Y:\master-documentation\azat\dp\tab\25mg\2-drug-product\azat-tab-25mg-dp-1-bmi-[d-6475703-01-11].pdf
Y:\master-documentation\azat\dp\tab\25mg\2-drug-product\azat-tab-25mg-dp-1-bmi-[d-6475703-01-11].pdf
Is there a function to get the text after the 7th occurrence of \?
是否有在第 7 次出现后获取文本的函数\？
回答by quantum285
The split function splits a string into an array of whatever size is required. The first argument for Split is the text to split and the second is the delimiter.
split 函数将字符串拆分为任意大小的数组。Split 的第一个参数是要拆分的文本，第二个参数是分隔符。
Sub test()
Dim strFound As String
Text = "Y:\master-documentation\azat\dp\tabmg-drug-product\azat-tab-25mg-dp-1-bmi-[d-6475703-01-11].pdf"
strFound = Split(Text, "\")(7)
End Sub
回答by barry houdini
If you have data in cell A1 the following worksheet formula extracts everything after the 7th "\"
如果单元格 A1 中有数据，则以下工作表公式会提取第 7 个“\”之后的所有内容
=REPLACE(A1,1,FIND("^^",SUBSTITUTE(A1,"\","^^",7)),"")
=REPLACE(A1,1,FIND("^^",SUBSTITUTE(A1,"\","^^",7)),"")
SUBSTITUTEfunction replaces the 7th "\"with "^^"[use any character or combination of characters that you know won't appear in the data]
SUBSTITUTE函数替换7号"\"与"^^"[使用字符的任意字符或组合，你知道会不会出现在数据]
...then FINDfunction finds the position of "^^"and allows REPLACEfunction to replace those characters and all before with nothing.
...thenFIND函数找到的位置"^^"并允许REPLACE函数用空替换这些字符和之前的所有字符。
回答by Simon
filename = Right(fullfilepath, Len(fullfilepath) - InStrRev(fullfilepath, "\"))
InStrRevfinds the first occurrance of a string starting the search from the end.
InStrRev从末尾开始搜索，查找第一个出现的字符串。
回答by Ron Rosenfeld
To return
返回
azat-tab-25mg
azat-tab-25mg
from your original string, I returned everything from the last"\" up to the third"-" following.
从您的原始字符串中，我返回了从最后一个“\”到第三个“-”之间的所有内容。
Worksheet Excel function:
工作表Excel功能：
=LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99)),
"-",CHAR(1),3),FIND(CHAR(1),SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(
A1,"\",REPT(" ",99)),99)),"-",CHAR(1),3))-1)
User Defined Function:
用户定义函数：
Option Explicit
Function Meds(S As String) As String
Dim S1 As Variant, S2 As Variant
S1 = Split(S, "\")
S2 = Split(S1(UBound(S1)), "-")
ReDim Preserve S2(0 To 2)
Meds = Join(S2, "-")
End Function
