[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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

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.

  • 2
1 Solution
Arno KosterCommented:
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

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

Best regards
Arno KosterCommented:
excellent !

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now