Jump to content

Build Theme!
  • Infected?


Hey there! :wub: Looks like you're enjoying the discussion, but you're not signed up for an account. When you create an account, we remember exactly what you've read, so you always come right back where you left off. You also get notifications, here and via email, whenever new posts are made. You can like posts to share the love. :D Join 92768 other members! Anybody can ask, anybody can answer. Consistently helpful members may be invited to become staff. Here's how it works. Virus cleanup? Start here -> Malware Removal Forum.

Try What the Tech -- It's free!


Splitting Strings in a Macro

  • Please log in to reply
3 replies to this topic

#1 K e n

K e n

    New Member

  • Authentic Member
  • Pip
  • 11 posts

Posted 13 February 2012 - 04:00 AM

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


Register to Remove

#2 Ax238


    Advanced Member

  • Visiting Tech
  • PipPipPipPip
  • 716 posts

Posted 28 February 2012 - 12:21 AM

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



#3 K e n

K e n

    New Member

  • Authentic Member
  • Pip
  • 11 posts

Posted 02 March 2012 - 05:06 AM

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

Edited by K e n, 02 March 2012 - 05:07 AM.

#4 Ax238


    Advanced Member

  • Visiting Tech
  • PipPipPipPip
  • 716 posts

Posted 03 March 2012 - 01:20 AM

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

Related Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users