Forum Discussion

Molekula's avatar
Molekula
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's avatar
    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)

HansVogelaar's avatar
HansVogelaar
MVP
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)

Molekula's avatar
Molekula
Copper Contributor
Aug 29, 2022

Good evening HansVogelaar,
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 receiveResult I expect
FABF - Google - worldwide - REPGoogle

CRIM - RAW - Yahoo - global - worldwide

Yahoo
FABF - DOM - RIM - drive - globaldrive.can.com 
limbo - FAB - RAW - Bloomberglimbo.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?

 

 

Thank you in advance

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 29, 2022

    Molekula 

    I don't see how we could do this, sorry.

    • Molekula's avatar
      Molekula
      Copper Contributor
      Aug 29, 2022

      HansVogelaar that's okay, I will just stick with your solution and have a small part of data on IF/manual filling.
      You helped a lot, thank you!

      • mtarler's avatar
        mtarler
        Silver Contributor
        Aug 29, 2022

        Molekula  What if you used the WEBSERVICE() function to see if it returns any information:

        =LET(
a,FILTERXML("<y><z>"&SUBSTITUTE(A27," ","</z><z>")&"</z></y>","//z"),
CONCAT(IF(IFERROR(LEN(WEBSERVICE(a)),FALSE),a,"")))

        This will only work if the websites can be acquired by the webservice.

        Alternatively you could use this sort of technique to just add a ?? or something in front of the result.

Resources