VBA Express : Excel - Create an array of sub-strings from a string

Excel

Create an array of sub-strings from a string

Ease of Use

Easy

Version tested with

2016

Submitted by:

mdmackillop

Description:

An array of descending size to user set limit listing all sub-strings from a text string

Discussion:

Compare text strings from two sources with variations in presentation.

Code:

instructions for use

			Function TextArray(Data As String, Limit As Long) 
    Dim i As Long, j As Long, k As Long, m As Long, n As Long 
    Dim arr() 
    i = Len(Data) 
    k = Len(Data) - 1 
    ReDim arr(i * (i + 1) / 2) 
    For m = i To Limit Step -1 
        For j = 1 To i - k 
            arr(n) = Mid(Data, j, m) 
            n = n + 1 
        Next j 
        k = k - 1 
    Next m 
    ReDim Preserve arr(n) 
    TextArray = arr 
End Function

How to use:

Call the function from comparison sub routine.

Test the code:

Refer to sample file

Sample File:

StringList.zip 15.42KB

Approved by Jacob Hilderbrand

This entry has been viewed 15 times.