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 29, 2022

Good evening Detlef_Lewin ,
As for now everything is working perfectly, so thank you once again.
But I am looking a bit in the future, and doubt if there is any possibility to solve the following issue that I may face:

1. Apart from the clean data (table in the post above) I receive, there is a high chance of dirty data like that:

port data I receive	Result I expect
FABF - Google - worldwide - REP	Google
CRIM - RAW - Yahoo - global - worldwide	Yahoo
FABF - DOM - RIM - drive - global	drive.can.com
limbo - FAB - RAW - Bloomberg	limbo.bloomberg.com
FABF - google - RIM - drive-global-	google.drive.global
FABF - google - RIM - drive-global-NL	google.drive.global.nl
CRIM - RAW - Yahoo - global - worldwide (only)	Yahoo.worldwide (only)
CRIM - RAW - Yahoo - global - worldwide-(only)-certainperiod	Yahoo.worldwide (only) certainperiod

Where there is no clear reference to the website but at least a little source from where it may came from. And thus it will help me to avoid potential blanc links....

2. Is there any way to integrate that in your formula?

My solution:

I have added IFERROR=0 to your function to filter the dirty data and added multiple IF columns based on each criteria, but the file looks messy and I am no satisfied with this automation.

Thank you in advance

Forum Discussion

Extract text between two characters

Resources