Forum Discussion

Copper Contributor

Aug 23, 2022

Solved

Extract text between two characters

Hello everybody, Recently I encountered a quirky problem while trying to retrieve some data from a cell. Hope you may help me. So I have an export csv table as following (5 rows for example): ...

BI & Data Analysis

excel

Formulas and Functions

HansVogelaar

Aug 23, 2022

Molekula

Here is a small custom VBA function:

Function ExtractURL(ByVal s As String) As String
    Dim p1 As Long
    Dim p2 As Long
    Dim p3 As Long
    s = Replace(s, Chr(160), " ")
    p1 = InStr(1, s, ".")
    p2 = InStrRev(s, " ", p1 - 1)
    p3 = InStr(p1 + 1, s, " ")
    If p3 > 0 Then
        s = Mid(s, p2 + 1, p3 - p2 - 1)
    Else
        s = Mid(s, p2 + 1)
    End If
    If Left(s, 4) <> "www." Then
        s = "www." & s
    End If
    ExtractURL = s
End Function

Use like this: in a cell formula:

=ExtractURL(A2)

Detlef_Lewin

Silver Contributor

Aug 23, 2022

Molekula

=LET(
a,FILTERXML("<y><z>"&SUBSTITUTE(A2," ","</z><z>")&"</z></y>","//z"),
b,SEARCH(".",a),
LOOKUP(9^99,b,a))

Molekula

Copper Contributor

Aug 24, 2022

Thank you very much Detlef_Lewin you function works like a charm and if I could mention your answer as a best answer I would do it immediately.
Thank you.