Remove string before a certain character

[copyt]

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

String = sp|P46915|COTSA_BACSU

Output = COTSA
Any help would be appreciated :bow:

Thanks!!!

[Kenneth Hobs]

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

[copyt]

@ Kenneth Hobs (http://www.vbaexpress.com/forum/member.php?u=3661) 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.

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

[snb]

It's not clear what you want:


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


or

sub snb()
msgbox split(replace("sp|P46915|COTSA_BACSU","_","|"),"|")(ubound(split(replace("sp|P46915|COTSA_BACSU","_","|"),"|"))-1)
end sub

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