Extract a variable number of parts of text from a string

[Harris@Z]

Hi,
I hope someone can assist - I have searched Google and cannot find a function that could do this for me.

I would like to find a function that can extract all text between two 'delimiters' in a string.

In other words:
String = "Easily extract #text# between two #strings# with this #VBA Function#. This user-defined function #(UDF)# can extract"

Result:
text
strings
VBA Function
(UDF)

Is this possible?
Thanks
Harris

 

[MajP]

Did your search not return the VBA Split() function? I find that hard to believe. If I type your question directly into Google it provides that.

 

[arnelgp]

try this code and put in a Module:

' ChatGPT
Function ExtractTextBetweenHashes(ByVal inputText As String) As String
    Dim regex As Object
    Dim matches As Object
    Dim match As Object
    Dim result As String

    ' Create a RegExp object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "#(.*?)#"
    regex.Global = True ' Ensure it matches all occurrences

    ' Execute the regular expression
    If regex.Test(inputText) Then
        Set matches = regex.Execute(inputText)
        For Each match In matches
            ' Append the matched text (excluding the hashes)
            result = result & vbCrLf & match.SubMatches(0)
        Next match
    End If
    ExtractTextBetweenHashes = Mid$(result, 3)
End Function

To test on Immediate window:

?ExtractTextBetweenHashes("Easily extract #text# between two #strings# with this #VBA Function#. This user-defined function #(UDF)# can extract")

 

[Harris@Z]

@MajP - Thanks, I am aware of the VBA Split() function but could work out how to extract 'all from the string.

@arnelgp - You are a star! This is perfect, thank you! I found something similar doing a Google search, but the function only drew the first instance from the string.

Much appreciated, as usual, for the brilliant members of AccessWorld who solved this novice's difficulty, and so fast!

 

[Harris@Z]

Hi @arnelgp

Is it possible to return the intermediate value, i.e., #text# #strings# #VBA Function# as individual values rather than in one go?

Thanks!
Harris

 

[Gasman]

Just split the returned string by vbCrLf and pick the one you want?

 

[DickyP]

If you need anything more than Split() - perhaps multiple delimiters for instance - then use Regular expressions.

 

[arnelgp]

you can add this another function:

Public Function GetSubstringBetweenHash(ByVal inputText As String, ByVal Ordinal As Integer) As String
    Dim ret As String, var As Variant
    If Ordinal < 1 Then
        Exit Function
    End If
    ret = ExtractTextBetweenHashes(inputText)
    If Len(ret) <> 0 Then
        var = Split(ret, vbCrLf)
        If Ordinal - 1 <= UBound(var) Then
            GetSubstringBetweenHash = var(Ordinal - 1)
        End If
    End If
      
End Function

call the above function instead.

to use:
' to get the first substring

GetSubstringBetweenHash("Easily extract #text# between two #strings# with this #VBA Function#. This user-defined function #(UDF)# can extract",1)

 

[NauticalGent]

Add them to a collection and then use them as you will

 

[The_Doc_Man]

It is not for the faint of heart, but if you would like a text-parser that would be a tool for breaking down ANSI text or USASCII strings into component parts, you could always look at this:

Text Parsing Support as Class Module

I've been working on something I used a lot at work to help me more easily decipher complex text inputs. For the Navy, I used this as a part of a program to analyze IIS logs, decode complex names, and I even once used a variant of this under OpenVMS to build a command-line calculator for the...

www.access-programmers.co.uk

This wouldn't do what you want by itself, but you could use it to take apart strings from inside a loop, and in your case it would have no trouble with the hash-tag/octothorpe character as a potential character. You would use it to accumulate text parts and, when you reach a particular character such as # (or any other punctuation, actually), you could decide what to do with what you have accumulated.

 

[NauticalGent]

Disclaimer: This is a ChatGPT generated response and has not been tested. If this is the way you want to go, try it and if it needs some tweaking and you get get stuck, post back here for some additional help.

Paste this is a standard Module:

Function ExtractTextWithDelimiters(ByVal InputString As String, ByVal StartDelimiter As String, ByVal EndDelimiter As String) As Collection
    Dim Results As New Collection
    Dim StartPos As Long
    Dim EndPos As Long
    Dim TempString As String
    
    StartPos = InStr(1, InputString, StartDelimiter)
    
    Do While StartPos > 0
        StartPos = StartPos + Len(StartDelimiter) ' Move past the start delimiter
        EndPos = InStr(StartPos, InputString, EndDelimiter)
        
        If EndPos > 0 Then
            TempString = Mid(InputString, StartPos, EndPos - StartPos)
            Results.Add TempString
            StartPos = InStr(EndPos + Len(EndDelimiter), InputString, StartDelimiter)
        Else
            Exit Do
        End If
    Loop
    
    Set ExtractTextWithDelimiters = Results
End Function

Then, to call it run this sub:

 Sub TestExtract()
    Dim Results As Collection
    Dim Item As Variant
    Dim TestString As String

    TestString = "Easily extract #text# between two #strings# with this #VBA Function#. This user-defined function #(UDF)# can extract"
    
    Set Results = ExtractTextWithDelimiters(TestString, "#", "#")
    
    For Each Item In Results
        Debug.Print Item ' Outputs each extracted value to the Immediate Window
    Next Item
End Sub

You could easily add arguments to this sub so that it works throughout your project, but this should help you get started

 

[Gasman]

TBH I would rather go with @arnelgp 's regex method.

 

[DHookom]

I like NauticalGent’s code since it can be used with any delimiter as well as beginning and ending characters. I would not hard code the #.

 

[cheekybuddha]

I would not hard code the #.

It's not difficult to adjust arnel's function to build the Regex pattern using a parameter input for the delimiter

 

[Gasman]

Also I have to think @arnelgp 's code is more efficiient?

 

[theDBguy]

Also I have to think @arnelgp 's code is more efficiient?

If we're talking about speed, after I wrote the following article a while back,

Extract Data Using Regular Expressions

Earlier, I wrote this article for extracting e-mail addresses from a memo field using regular expressions. What it was able to do is retur...

thedbguy.blogspot.com

I published the results of my tests here.

RegEx vs Character Loop

As is often the case with database management, we invariably have to clean up the data at some point. This demo was inspired by a thread dis...

thedbguy.blogspot.com

 

[Gasman]

Thank you @theDBguy
Strangely the download links are not working for me?

Plus 2024 seems to have disappeared? I was looking for another article with download links to try those.

I also tried leaving a comment, when it asked me to sign in with Google, which I did, and then it comes back with Sign in with Blogger? :-(

 

[theDBguy]

Thank you @theDBguy
Strangely the download links are not working for me?

Plus 2024 seems to have disappeared? I was looking for another article with download links to try those.
View attachment 118245

I also tried leaving a comment, when it asked me to sign in with Google, which I did, and then it comes back with Sign in with Blogger? :-(

View attachment 118246

Hi @Gasman. Thanks for the heads up. I'll check them out...

 

[theDBguy]

Hi @Gasman. Thanks for the heads up. I'll check them out...

Here's the speed test file, if anyone is interested.

 

[NauticalGent]

All Arnel's code needs is to populate a collection so that it returns all of the items and life would be good. I prefer RegEx as well for the record.