Remove string before a certain character

[copyt]

Hello I need to remove string before a certain character as shown below.

[vba]String = sp|P46915|COTSA_BACSU

Output = COTSA[/vba]
Any help would be appreciated

Thanks!!!

[Kenneth Hobs]

[VBA]
Sub t()
Dim s As String, a() As String
s = "sp|P46915|COTSA_BACSU"
a() = Split(s, "|")
MsgBox Split(a(UBound(a)), "_")(0)
End Sub[/VBA]

[copyt]

@ Kenneth Hobs Thank you very much!!!

[Kenneth Hobs]

I prefer arrays but some like string parsing methods. The function can be used as a UDF. The array method can be made into a UDF as well if needed.

[VBA]Sub t2()
Dim s As String, a() As String
s = "sp|P46915|COTSA_BACSU"
MsgBox Mid(s, InStrRev(s, "|") + 1, InStrRev(s, "_") - InStrRev(s, "|") - 1)
End Sub

Sub t3()
Dim s As String, a() As String
s = "sp|P46915|COTSA_BACSU"
MsgBox MidStr(s, "|", "_")
End Sub

'Finds mid string from sTo and then back to sFrom. So, make sTo unique.
'As a UDF: =MidStr(A1, "|", "_")
Function MidStr(str As String, sFrom As String, sTo As String, Optional toOffset As Integer = 0) As String
Dim strSub As String, sBegPos As Long, sEndPos As Long

sEndPos = InStr(str, sTo) - toOffset
strSub = Left(str, sEndPos)
sBegPos = InStrRev(strSub, sFrom) + 1

MidStr = Mid(strSub, sBegPos, sEndPos - sBegPos)
End Function[/VBA]

[snb]

It's not clear what you want:

[vba]
sub snb()
msgbox split(replace("sp|P46915|COTSA_BACSU","_","|"),"|")(2)
end sub
[/vba]

or
[vba]
sub snb()
msgbox split(replace("sp|P46915|COTSA_BACSU","_","|"),"|")(ubound(split(replace("s p|P46915|COTSA_BACSU","_","|"),"|"))-1)
end sub[/vba]

or something else....

[Paul_Hossler]

Hello I need to remove string before a certain character as shown below.

VBA:

String = sp|P46915|COTSA_BACSU Output = COTSA

What is the special character?

a. If it's the underscore _ , and you remove the string before that you'd get BACSU

b. If it's the pipe |, then (assuming it's the last) you'd get COTSA_BACSU

Neither one will get you just COTSA

Paul