Writing a , - delimited,  or ; - delimited string to an array

Posted on 2011-10-19
Last Modified: 2012-05-12
Hi, I have shapesheet cells with data strings like this:
1,234,34,236,45,863,5,1,89 or like this: 1;644;34;26;45;92871;89
(the type of delimiter is not constrained; white space can be added; the data is integer).

I want to compare strings and identify those with AT LEAST ONE integer in common. The relative positions of integers within the strings are irrelevant.

- Should I should first convert the shapesheet data strings to arrays, and then compare them?
- If so, what is the simplest way of converting a string from a shapesheet cell into a VBA array?
- Are there comparision functions that are useful for this task?

(My home made string parser enumerate integers individually, but it has given me problems when I seek to output the enumerated data in the form of an array. I suspect a ReDim problem, but want to ask about best approaches before further attempts to pep-up my code..)

I use vba, not VB.

Question by:Kelvin4
    LVL 19

    Accepted Solution

    there are a number of approaches to take apart the strings, such as

     - regular expressions
     - instr function
     - split function

    the split function would be the easiest approach, it is basically as simple as

    result_array = split(input_data_string, ",") or
    result_array = split(input_data_string, ";")

    you can then loop through the string arrays and find matches

    such as in
    Function containsMatch(string1 As String, string2 As String) As Boolean
        If InStr(string1, ",") > 0 Then array1 = Split(string1, ",") Else array1 = Split(string1, ";")
        If InStr(string2, ",") > 0 Then array2 = Split(string2, ",") Else array2 = Split(string2, ";")
        For Each item1 In array1
            For Each item2 In array2
                If item1 = item2 Then
                    '-- a match has been found !
                    containsMatch = True
                    Exit Function
                End If
            Next item2
        Next item1
        '-- no match has been found
        containsMatch = False
    End Function

    Open in new window


    Author Closing Comment

    Hi  Akoster,
    Many thanks for the instant response: it worked first time.

    Best regards
    LVL 19

    Expert Comment

    excellent !

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Periodically someone asks me whether there’s a way to automatically convert all of the pages in a Visio drawing to PowerPoint slides. There have even been a few times when I’ve wanted to do that myself but I never really had enough incentive to figu…
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now