String Manupulation in VBA

Question

I have single column that needs to be split to multiple, like Text-to-columns in excel. However there is a small challenge. Conventional delimiter will not work. Consider the string below

Original: Domain\Domain Admins Domain2\User Group Domain3\Developers .....(And so on)
Required: Domain\Domain Admins | Domain2\User Group | Domain3\Developers .....(And so on)

The pipe in the required string means that it needs to be split here and copied to next column as per the length of the string.

I have the list in column A with 506 rows. I used following formula to check the occurance of "\" i column B, count ranges from 0-66

=LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))

I need help to code following logic

Find "\" in the string
Find the Space just before the "\" and split

I used following code but it doesn't serve the purpose

Range("A1:A506").Select
Selection.TextToColumns

Please help with a code that keeps points 1 and 2 in mind.

I tried to reverse the string using following code 'Function Reverse(str As String) As String Reverse = StrReverse(Trim(str)) End Function' however stuck at how to find \ and then split at the following space after \ — Arvinder, Jul 18 '14 at 03:30
where did this text come from? it would be a lot easier if you could add quotes before it gets concatenated — Luke Hoffmann, Jul 18 '14 at 03:33
@ Luke, Hi, The text is export from SCCM reports that gives the list of all the objects in an AD Forest. I'm trying to filter the user groups from there. — Arvinder, Jul 18 '14 at 04:35
Can you not find `Group Domain`, replace it with `Group @ Domain` (assuming you dont have `@` in your string) and then split on `@`? Then you can replace back. No VBA needed, although this procedure can be automated. — Ioannis, Jul 18 '14 at 12:24

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Here is a function which you can use, comments + code are self-explanatory

Code:

Function SplitThis(InputStr As String) As Variant
    
    Dim SplitStr() As String, SubStr() As String, RightmostStr As String
    Dim OutputStr() As String
    
    Dim Delim_Level1 As String, Delim_Level2 As String, Delim_Cut As String
    
    Delim_Level1 = "\"
    Delim_Level2 = " "
    Delim_Cut = "}"         ' Any character you are guaranteed not to find in your original string
    
    'InputStr = "Domain\Domain Admins Domain2\User Group Domain3\Developer"
    SplitStr = Split(InputStr, Delim_Level1)
    
    ' SplitStr(0) = "Domain"
    ' SplitStr(1) = "Domain Admins Domain2"
    ' SplitStr(2) = "User Group Domain3"
    ' SplitStr(3) = "Developer"
    
    Dim i As Long
    For i = 1 To UBound(SplitStr) - 1                   ' i.e. 1 --> 2
        SubStr = Split(SplitStr(i), Delim_Level2)
        RightmostStr = SubStr(UBound(SubStr))
        
        ReDim Preserve SubStr(0 To UBound(SubStr) - 1)
        
        SplitStr(i) = Join(SubStr, Delim_Level2) & Delim_Cut & RightmostStr
        
    Next i
    
    ' SplitStr(0) = "Domain"
    ' SplitStr(1) = "Domain Admins{Domain2"
    ' SplitStr(2) = "User Group{Domain3"
    ' SplitStr(3) = "Developer"
    
    ' These are joined to create:
    ' "Domain\Domain Admins}Domain2\User Group}Domain3\Developer"
    '
    ' Which is split at the character "}" to create the output
    
    OutputStr = Split(Join(SplitStr, Delim_Level1), Delim_Cut)
    
    Dim OutputVariant() As Variant
    ReDim OutputVariant(0 To 0, 0 To UBound(OutputStr))
    
    For i = 0 To UBound(OutputStr)
        OutputVariant(0, i) = OutputStr(i)
    Next i
    
    SplitThis = OutputVariant
End Function

Usage:

Cell A1   : "Domain\Domain Admins Domain2\User Group Domain3\Developer"
Cell A3:C3: {=SplitThis(A1)}       ' Array formula

Screenshot:

enter image description here

Nagging Doubt: I have this feeling that the most efficient way to do this is by using some Regex based solution, but for now this should do.

Curiosity: can you not directly Split with Domain@ (where @ is a number wildcard)? — Noldor130884, Jul 18 '14 at 11:56
Actually I did look for some wildcard approach, but couldn't think of something that early in the day! Please do post a direct split as that'll be most efficient (in lines-of-code, at least). Also, it'll be a bit more complex than `Domain@` because the `Domain` itself would be a variable string — hnk, Jul 18 '14 at 12:06

String Manupulation in VBA

1 Answers1