Forum Discussion

Zdenek_Moravec's avatar
Zdenek_Moravec
Brass Contributor
Oct 27, 2024
Solved

How to extract multiple occurrences of a text enclosed between two same characters

Dear colleagues, I have a cell with plenty of text. Some part of the text is enclosed between tags >> and <<. The task is to extract all these strings to one cell, separated by new line. Notes ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Oct 28, 2024

    Zdenek_Moravec 

    I hope this works for you (with the text in A5):

     

    =LET(w, TEXTSPLIT(A5, "!"), n, COUNTA(w), s, SEQUENCE(, n), f, FILTER(w, ISEVEN(s)), TEXTJOIN(CHAR(10), TRUE, f))

Kidd_Ip's avatar
Kidd_Ip
MVP
Oct 28, 2024

Zdenek_Moravec 

 

Try below to extract the first occurrence:

 

=MID(A2, FIND(">>", A2) + 2, FIND("<<", A2) - FIND(">>", A2) - 2)

 

Follow to handle extract multiple occurrences:

 

Function ExtractTextBetweenTags(cell As Range) As String
Dim text As String
Dim result As String
Dim startPos As Long
Dim endPos As Long

text = cell.Value
result = ""

Do While InStr(text, ">>") > 0
startPos = InStr(text, ">>") + 2
endPos = InStr(text, "<<")
If endPos > startPos Then
result = result & Mid(text, startPos, endPos - startPos) & vbNewLine
text = Mid(text, endPos + 2)
Else
Exit Do
End If
Loop

ExtractTextBetweenTags = result
End Function

Resources