String Manipulation - Split into multiple variables

Hello,

I have a string that is pulled in from a 3rd part app. It ends up looking like this:

1234-56JUL10 2345678JUL10 3456789JUL10 876543JUL10 987654JUL10

It is all one string.... I need this to be able to have a variable that can be updated to the position in the string. So for example I need the 3rd portion is there any way to specifically pull the 3rd section of the string?

To explain a little, each of these are basically "claims" and I need to be able to check them one at a time which I would use a counter variable like d = d + 1. I know I could pull this value into a cell, use text to columns and then just use Cell to keep moving over 1 column until I found what I needed, but is there a way in VBA without pasting the values into a cell and using text to columns?

Edit: See end of thread for more info. I can't easily use a function.

Dulanic,

Detach/open workbook SplitData - Dulanic - EF744090 - SDG12.xlsm and run macro SplitData.

You only gave one example of the string.

I assume that all your data in column A, except for the beginning and ending spaces, contains 24 space characters between the strings you need.

Hello Dulanic,

This macro is a User Defined Function that will allow a variable number of spaces before and after the claim numbers. Copy this code into a standard VBA Module in your workbook.

'Written: September 01, 2010
'Author:  Leith Ross

Function GetClaim(ByRef Claims As Range, ByVal Position As Integer) As String

  Dim Cnt As Integer
  Dim Matches As Object
  Dim RegExp As Object
  Dim S As String, Text As String
  
    Application.Volatile
    
    Set RegExp = CreateObject("VBScript.RegExp")
      RegExp.Global = True
      RegExp.IgnoreCase = True
      RegExp.Pattern = "\s*(\S+)\s+(.*)"
    
    Text = Claims.Value
    
      Do While RegExp.Test(Text)
        S = RegExp.Replace(Text, "$1")
        Text = RegExp.Replace(Text, "$2")
        Cnt = Cnt + 1
        If Cnt = Position Then GetClaim = S
      Loop
    
End Function

Macro Example

  'Cell A1 Contains...
               8927-27JUL10                        4601107JUL10                        1783421JUL10                        3511826JUL10                        9886-09JUL10

  'CellB1 has the Formula...
   =GetClaim(A1, 3)

  'The result is...
   1783421JUL10

Leith Ross,

Very nicely done.

I just received:
The book 'Sams Teach Yourself Regular Expressions in 10 Minutes' by Ben Forta

Can you suggest any other books on the subject?

Hello Stanley,

Sorry, I haven't got any books on Regular Expressions, nor have I read through any. I have learned it through using it. Most of the information I have seen has been for Java, or PHP. There seems to be little specifically for VBScript RegExp. I will have to check out the Sams publication. Thanks for information.

Leith Ross,

You are very welcome.

How would I, you, change your pattern, to work with a long string that does not end in a space or spaces?

  RegExp.Pattern = "\s*(\S+)\s+(.*)"

Another UDF might be

Function GetClaimFrom(rng As Range, Optional Position As Long, Optional Delimiter As String)
    Dim arr As Variant
    
    If Position = 0 Then Position = 1
    If Delimiter = "" Then Delimiter = " "
    
    arr = Split(rng, Delimiter)
    If Position > UBound(arr) + 1 Then
        GetClaimFrom = ""
    Else
        GetClaimFrom = arr(Position - 1)
    End If
End Function

Or called in VBa

myVar = GetClaimFrom(Range("A2"))

will return the first value

myVar = GetClaimFrom(Range("A2"),2)

Returns the second

myVar = GetClaimFrom(Range("A2"),1,",")

Uses "," as the separator instead of the default " " (Space).

Hope this helps

I think that might do it. Just use a variable so I can do Your UDF doesn't work for me, however, I think I can twist Leith Ross's into doing what I need just by setting it like so:

myVar = GetClaim(Range("A2" & " "), v)
v = v + 1

to roll through each of them until the result is what I need or I hit a certain number.

I appreciate all the help and will mark solved once I get a chance to test and implement.

Not quite there, I am getting closer This below code using Leith's code. However, it stops 1 claim prior to the last claim. I tried

Sub test()
Dim v As Integer

v = 1

Do Until GetClaim(Range("A1" & " "), v) = ""
Range("A" & v + 1).Value = GetClaim(" " & Range("A1") & " ", v)
v = v + 1
Loop

End Sub

This throws an error. I was hoping to add a leading and trailing space to force it to work. Using just adding the trailing space didnt seem to change the results.

Using =GetClaim($A$1 & " ",1) just throws a #VALUE result.

The preferable way for this to work for me would be:

Sub test()
Dim v As Integer

v = 1

Claims = code to pull string from other app

Do Until GetClaim(Claims, v) = ""
Range("A" & v + 1).Value = GetClaim(Claims, v)
v = v + 1
Loop

End Sub

I can do this to force it, but then I just keep adding spaces each time it's run.

Sub test()
Dim v As Integer

v = 1

Range("A1").Value = " " & Range("A1").Value & " "


Do Until GetClaim(Range("A1" & " "), v) = ""
Range("A" & v + 1).Value = GetClaim(Range("A1"), v)
v = v + 1
Loop

End Sub

It seems to work for me.

See attached workbook

I'm not sure what you are trying to do

However try this modification to the UDF and apply it in VBa as explained in post #11

Function GetClaimFrom(rng As Range, Optional Position As Long, Optional Delimiter As String)
    Dim arr As Variant
    
    If Position = 0 Then Position = 1
    If Delimiter = "" Then Delimiter = " "
    
    arr = Split(WorksheetFunction.Trim(rng), Delimiter)
    If Position > UBound(arr) + 1 Then
        GetClaimFrom = ""
    Else
        GetClaimFrom = arr(Position - 1)
    End If
End Function

Sorry if I did not explain well enough. My goal is to avoid using the worksheet itself to do this, the string is originally pulled in to a variable via code to grab it from the 3rd party app. I want to try and avoid actually putting the value on the worksheet because noone needs to see it until it finds a match, does this make sense? Basically is there a way to make it allow the 1st variable for GetClaimFrom allow a variable input instead of a range input?

Sub test()
    Dim v As Integer
    
    v = 1
    
    Claims = Code 'Code to get the string of claims as shown before as a variable
    
    Do
        Claim = GetClaim(Claims, v)
        'Code goes here to check 3rd party app for a match of a certain thing
        
        If Match Found Then Do xyz
        
        'This is code to update _
        something on the worksheet pasting the claim that it found the match on.
        
        End If
        v = v + 1
    Loop Until GetClaim(Claims, v) = "" Or MatchFound = True

End Sub

Got it, sorry for all the extra work and me not explaining it that well. This was all I needed, i just haven't used Split all that much and didn't realize all I needed to do was provide the delimiter and the position after. I do have to admit the (v) for the position afterword kind of throws me off and not sure how that works out. I mean I understand what it did, just the layout of it doesnt make sense to me.

Sub test()
    Dim v As Integer
    
    v = 1
    
    Claims = Code 'Code to get the string of claims as shown before as a variable
    
    Do
        Claim = Split(WorksheetFunction.Trim(Claims), " ")(v)
        'Code goes here to check 3rd party app for a match of a certain thing
        
        If Match Found Then Do xyz
        
        'This is code to update _
        something on the worksheet pasting the claim that it found the match on.
        
        End If
        v = v + 1
    Loop Until GetClaim(Claims, v) = "" Or MatchFound = True

End Sub

Try this in a blank sheet

Sub SplitString()
    Dim Posn As Integer
    Dim str2search As String
    Dim arr2search As Variant
    
    'From whatever source
    str2search = "            8927-27JUL10                        4601107JUL10                        1783421JUL10                        3511826JUL10                        9886-09JUL10"
    str2search = WorksheetFunction.Trim(str2search)
    
    arr2search = Split(WorksheetFunction.Trim(str2search), " ")
    
    For Posn = LBound(arr2search) To UBound(arr2search)        
         Range("A" & Posn + 2) = arr2search(Posn)
         If InStr(1, arr2search(Posn), "-") > 0 Then
            Range("B" & Posn + 2) = Left(arr2search(Posn), Len(arr2search(Posn)) - 8)
         Else
            Range("B" & Posn + 2) = Left(arr2search(Posn), Len(arr2search(Posn)) - 7)
         End If
         Range("C" & Posn + 2) = Right(arr2search(Posn), 7)
    Next
    
End Sub

This, based on your sample string, will split your string then divide again into Code and date (as a string).

WorksheetFunction.Trim() is used rather than just Trim() because it will remove all extra spaces, not just the leading and trailing spaces.

Hope this helps

Hello Dulanic,

Here is the corrected version of my macro. The fix was to add a space to the end of the Claims and change Claims from a Range to a String.

'Written: September 01, 2010
'Updated: September 02, 2010
'Author:  Leith Ross

Function GetClaim(ByVal Claims As String, ByVal Position As Integer) As String

  Dim Cnt As Integer
  Dim Matches As Object
  Dim RegExp As Object
  Dim S As String, Text As String
  
    Application.Volatile
    
    Set RegExp = CreateObject("VBScript.RegExp")
      RegExp.Global = True
      RegExp.IgnoreCase = True
      RegExp.Pattern = "\s*(\S+)\s+(.*)"
    
    Text = Claims & " "
    
      Do While RegExp.Test(Text)
        S = RegExp.Replace(Text, "$1")
        Text = RegExp.Replace(Text, "$2")
        Cnt = Cnt + 1
        If Cnt = Position Then GetClaim = S
      Loop
      
               
End Function

OK so I am reopening this thread as I am finding what I was doing isn't working.... I was using this:

Claim = Split(WorksheetFunction.Trim(Claims), " ")(v)

v is a variable interger. The problem comes in if it is not able to find a match it tried to keep going to let's say it does v as 6. This results in an error since 6 is not a valid option since it only split 5 claims. Is there a way to prevent this error?

I guess what I am wants to find out is can I get a count for "Claim" since it breaks down into multiple variants and I want to know how many? Like there is Claim(0) through Claim(5) so I want to know the highest variant is 5 so I can throw a catch to stop at 5. Sometimes 5 mights be 4 or 7....

Thanks to those for functions, but functions arent going to really do what I need since I am trying to get variables purely in VBA and not involve a worksheet.

What would you want to return if it were beyond the last value -- a null string?

Claim = Split(WorksheetFunction.Trim(Claims) & Space(255), " ")(v)