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

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 372

# 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.

Thanks,
Kelvin
0
Kelvin4
• 2
1 Solution

Commented:
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
``````
0

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

Best regards
Kelvin
0

Commented:
excellent !
0

## Featured Post

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