在 VBA 中使用 LastIndexOf() 拆分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27418429/
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
Splitting String With LastIndexOf() in VBA
提问by Addy
How to split A string By LastIndexOf()in VBA
如何LastIndexOf()在 VBA 中拆分字符串
I have a string which can have these kind of values
我有一个可以有这些值的字符串
Dim input As String
input = "customName_D3"
input = "Custom_Name_D3"
input = "my_Custom_Name_D3"
like it can have many "_"in it but after last"_"it contains cell name
I want to split this string to get cell name and other remaning part as two different strings
something like
就像它可以有很多"_"，但最后"_"它包含单元格名称我想拆分这个字符串以获得单元格名称和其他剩余部分作为两个不同的字符串，例如
cellName = D3
remainingString = my_custom_Name
回答by Jean-Fran?ois Corbett
Dim strInput As String
Dim strName As String
Dim strCellAddress As String
Dim iSplit As Integer
strInput = "my_Custom_Name_D3"
iSplit = InStrRev(strInput, "_")
strName = Left(strInput, iSplit - 1)
strCellAddress = Mid(strInput, iSplit + 1)
MsgBox "Name: " & vbTab & strName & vbCrLf & "Cell: " & vbTab & strCellAddress
回答by Rubidium 37
Please try the following code:
请尝试以下代码：
Public Function SplitAtLast(ByVal sText As String, ByVal sMatch As String, ByRef sLeft As String, ByRef sRight As String) As Integer
Dim nIndex As Integer
' Search for sMatch within sText
nIndex = InStrRev(sText, sMatch)
If nIndex > 0 Then ' sMatch was found as nIndex'th character
' Save all chars before sMatch
sLeft = Left$(sText, nIndex - 1)
' Save all chars after sMatch
sRight = Mid$(sText, nIndex + Len(sMatch))
Else ' sMatch was NOT found
' Save all chars into sLeft
sLeft = sText
sRight = ""
End If
' Returns position of found match
SplitAtLast = nIndex
End Function
To be called as
被称为
Dim sInput As String, sLeft As String, sRight As String, nIndex As Integer
sInput = "customName_D3"
nIndex = SplitAtLast(sInput, "_", sLeft, sRight)
MsgBox sInput & " : " & nIndex & ", <" & sLeft & ">, <" & sRight & ">"
sInput = "Custom_Name_D3"
nIndex = SplitAtLast(sInput, "_", sLeft, sRight)
MsgBox sInput & " : " & nIndex & ", <" & sLeft & ">, <" & sRight & ">"
sInput = "my_Custom_Name_D3"
nIndex = SplitAtLast(sInput, "_", sLeft, sRight)
MsgBox sInput & " : " & nIndex & ", <" & sLeft & ">, <" & sRight & ">"
Regards, Daniele.
问候，丹尼尔。
