Handling Strings with seperators

Hi there I am working in Excel 2010.

I am being passed some strings in my worksheet cells that the other programmers (DXL) promises will be in the format "string1;string2;string3"

I am trying to check that they have done this and have started with the following:

Function IsDOORSOutputFormat(Str As String) As Boolean


Dim reDOORSOF As New RegExp


reDOORSOF.Pattern = "\w{1,};\w{1,};\w{1,}" 'or "\S{1,};\S{1,};\S{1,}"?

reDOORSOF.Global = True
reDOORSOF.IgnoreCase = True

If reDOORSOF.Test(Str) Then IsDOORSOutputFormat = True Else IsDOORSOutputFormat = False

End Function

I don't think I can use /w because I can't limit the strings to use a space or "-" or whatever as word separators (just not ";").
However if I use \S I will not be able to tell if too many ";"s exist in Str.

To put this in context I will be moving forward by taking Strings 1, 2 and 3 and processing them separately so if you also have advice on an efficient way of separating them once the format is confirmed that would be very much appreciated. :friends: (but I could muddle something to together)

Welcome to the forum!

You can use Split() to parse the string into an array. You can use Ubound(Split(theString, ";")) to find the number of elements that were split. Since Split() returns a 0 based string array, UBound() would return 2 for the upper bound of the array elements for your example.

Thank you that was very helpful and much shorter than the direction I was heading. :hifive: