Extract Text after Second Underscore in Excel from right side-CodePudding

Need to extract text between first and second "_" from string in a cell.

Example - 88/12_PO/SP_SJ_#448491_WHITE_10A_60

Required to extract "10A" from above string

=MID(B2, SEARCH("_",B2)   1, SEARCH("_",B2,SEARCH("_",B2) 1) - SEARCH("_",B2) - 1)

CodePudding user response：

As you tagged VBA: This could be a UDF to use.
Default separator is "_", but you can specify any other separator character
Default piece is the 2nd last (index = -2), but you can ask for any other position

Function extract(word As String, Optional separator As String = "_", Optional ByVal index As Long = -2)
    ' index = 1 gives first word, 2 gives 2nd word...
    ' index -1 gives last word, -2 gives 2nd last word...
    Dim tokens() As String
    tokens = Split(word, separator)
    If index < 0 Then index = UBound(tokens)   index   1 Else index = index - 1
    If index >= 0 And index <= UBound(tokens) Then extract = tokens(index)
End Function

Just use a formula like =Extract(B2) in Excel

CodePudding user response：

Let me chuck in some options for you to consider:

VBA:

Not my forte, but I'd use the Split() option here:

Function SPLITTEXT(s As String, del As String, Optional indx As Long = 1) As String

If indx < 0 Then
    s = StrReverse(s)
    SPLITTEXT = StrReverse(Split(s, del)(Abs(indx) - 1))
Else
    SPLITTEXT = Split(s, del)(Application.Max(indx, 1)- 1)
End If

End Function

Or, maybe:

Function SPLITTEXT(s As String, del As String, Optional indx As Long = 1) As String

Dim r As Variant: r = Split(s, del)

If indx < 0 Then
    SPLITTEXT = r(UBound(r)   indx - 1)
Else
    SPLITTEXT = r(Application.Max(indx, 1) - 1)
    
End If

End Function

Call like =SPLITTEXT(A1,"_",-2) where I tried to mimic the TEXTBEFORE() 3rd parameter to let you pick an index from left or right.

Excel ms365:

=@TAKE(TEXTSPLIT(A1,"_"),,-2)

Excel 2013 and up for Windows:

=FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>")&"</s></t>","//s[last()]/preceding::*[1]")

Or:

=FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>")&"</s></t>","//s[position() = last()-1]")

CodePudding user response：

The *2 is the count from right side, so it will return the 2nd from right side. Changing it to *3 would return the 3rd from right side (WHITE in your example):

=TRIM(LEFT(RIGHT(SUBSTITUTE(B2,"_",REPT(" ",LEN(B2))),LEN(B2)*2),LEN(B2)))

CodePudding user response：

You can do it that way, if you need it in many steps :

Dim sMyString As String
Dim Pos1 As Long
Dim Pos2 As Long
Dim Result As String

sMyString = "88/12_PO/SP_SJ_#448491_WHITE_10A_60"

Pos1 = InStrRev(sMyString, "_")
Pos2 = InStrRev(sMyString, "_", Pos1 - 1)
Result = Mid(sMyString, Pos2   1, Pos1 - 1 - Pos2) 'this will return 10A

You can also put this in a function

Public Function ExtractString(sMyString As String) As String

Dim Pos1 As Long
Dim Pos2 As Long
Dim Result As String

Pos1 = InStrRev(sMyString, "_")
Pos2 = InStrRev(sMyString, "_", Pos1 - 1)
ExtractString = Mid(sMyString, Pos2   1, Pos1 - 1 - Pos2)

End Function