Splitting Strings in a Macro

Hi, everybody. Has anyone got a neat way of spliitng a given string of the pattern "=-10+11+12-13+14.5-0.13", into two strings separating the plus expressions from the minus? My solution attempts look very clumsy and precarious. K e n

Hi Ken,

I'd love to help you, but am unsure what the end result would be in your example. Is it that you want the two strings to be as follows?

Plus: +11+12+14.5

Minus: -10-13-0.13

If this is the case, you can get results similar to this with the following:

Dim str As String, result As Object
	Dim strMinus As String, strPlus As String
	str = "-10+11+12-13+14.5-0.13"
	
	Dim r As New RegExp
	With r
		.Pattern = "([+-]?[0-9]+(\.[0-9]+)?)"
		.Global = True
		Set result = .Execute(str)
	End With
	
	For i = 0 To result.Count - 1
		Dim val As String
		val = result(i).Value
		If (Left(val, 1) = "-") Then
			strMinus = strMinus & val
		Else
			strPlus = strPlus & val
		End If
	Next i

I tested and it works for the string you gave me, just turn the above into a function and pass the string in as a parameter. You'll also need to add a reference to the Microsoft VBScript Regular Expressions Library to your spreadsheet to get the regular expressions working.

Regards,

Ax

Thanks, Ax. Your code seems to work fine, although I have no experience with the methods you use. Can you tell me what advantage they might have over my best effort (as follows)? Public Function SplitAndJoin(sText) Dim vX As Variant, i As Long, negString As String, posString As String 'sText = "=-10+23-0,36+36,9-12,56" 'For test purposes vX = Split(Replace(Replace(Replace(sText, "-", "\-"), "+", "\+"), "=", ""), "\") For i = 0 To UBound(vX) If Left(vX(i), 1) = "-" Then negString = negString & vX(i) Else posString = posString & vX(i) End If Next i SplitAndJoin = posString & negString End Function Best regards K e n

Hi K e n, That would work fine too, there are trade-offs with both methods. I was giving you a method that I just came up with, it would probably run a lot slower than yours since it uses regular expressions, but you most likely wouldn't notice. I often find regular expressions easier to read and implement than chaining a lot of string replaces. Ax