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!

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

    Thank you very much HansVogelaar!
    Your answer corresponds exactly to what I was looking for even though it is a VBA code. (I am still learning to write in VB)
    Works like a charm,

Resources