CODEWITHSUNDEEP
excelvba
D3merzel
D3merzel

Reputation: 63

Seperating a character string

I want to separate a character string using the special characters in that string as cutting lines. After each division the next group of strings should be copied in the next column. The picture below shows how it should work.

enter image description here

My first approach doesn't work and maybe it's too complicated. Is there a simple solution to this task?

Sub SeparateString()

Dim i, j, k, counterA, counterB As Integer
Dim str1, str2 As String
Const Sonderz As String = "^!§$%&/()=?`*'_:;°,.-#+´ß}][{³²"
   
For i = 1 To Worksheets("Tabelle1").Range("A1").End(xlDown).Row

    counterA = 0

    For j = 1 To Len(Worksheets("Tabelle1").Range("A" & i))
    
        counterB = 0
        
        For k = 1 To Len(Sonderz)
        
            If Mid(Worksheets("Tabelle1").Range("A" & i), j, 1) = Mid(Sonderz, k, 1) Then
        
                counterA = counterA + 1
        
            End If
            
            If Mid(Worksheets("Tabelle1").Range("A" & i), j, 1) <> Mid(Sonderz, k, 1) And counterA = 0 And counterB = 0 Then
                                                   
                Worksheets("Tabelle1").Range("B" & i) = Worksheets("Tabelle1").Range("B" & i) & Mid(Worksheets("Tabelle1").Range("A" & i), j, 1)
                
                counterB = counterB + 1

            End If

        Next k
    
    Next j
       
Next i

End Sub

Upvotes: 0

Views: 82

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

You can also do this in Power Query which has a command to split by ANY delimiter.

In the code below, I generate a list of all possible special characters defined as characters NOT in the set of A..Za..z0..9 and you can easily add to that list by editing the code if you want to include other characters in the permitted list.

Edit: If you only have a few special characters, you can just hard-code the list, eg {"!","?"} instead of using List.Generate, but in your question you did not necessarily restrict the list of special characters, even though those are the only two showing in your examples

To use Power Query:

  • Select some cell in your Data Table
  • Data => Get&Transform => from Table/Range
  • When the PQ Editor opens: Home => Advanced Editor
  • Make note of the Table Name in Line 2
  • Paste the M Code below in place of what you see
  • Change the Table name in line 2 back to what was generated originally.
  • Read the comments and explore the Applied Steps to understand the algorithm
let

//change Table name in next line to reflect actual table name
    Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}}),

//Generate list of "special characters" for splitting
//the List.Contains function contains the non-special characters
splitterList = List.RemoveNulls(
    List.Generate(()=>0,
        each _ <= 255,
        each _ +1,
        each if List.Contains({"A".."Z","a".."z","0".."9"}, Character.FromNumber(_)) then null else Character.FromNumber(_))),

    splitIt = Table.SplitColumn(#"Changed Type", "Source", 
        Splitter.SplitTextByAnyDelimiter(splitterList))
in
    splitIt

enter image description here

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8375

Assuming the first data is in cell A2, I would go with the simple use of find() with left() mid() and right()

=left(A2,find("!",A2,1)-1)

then:

=mid(A2,find("!",A2,1)+1,find("?",A2,1)-find("!",A2,1)-1)

and:

=right(A2,len(A2)-find("?",A2,1))

Tested and working with one correction done.

Upvotes: 1

JvdV
JvdV

Reputation: 75840

If you are interested and you do have access to Microsoft 365's dynamic arrays:

enter image description here

Formula in B1:

=LET(X,MID(A1,SEQUENCE(LEN(A1)),1),TRANSPOSE(FILTERXML(CONCAT("<t><s>",IF(ISNUMBER(FIND(X,"^!§$%&/()=?`*'_:;°,.-#+´ß}][{³²")),"</s><s>",X),"</s></t>"),"//s")))

Or nest a SUBSTITUTE() if you need to return string variables:

enter image description here

=LET(X,MID(A1,SEQUENCE(LEN(A1)),1),TRANSPOSE(SUBSTITUTE(FILTERXML(CONCAT("<t><s>'",IF(ISNUMBER(FIND(X,"^!§$%&/()=?`*'_:;°,.-#+´ß}][{³²")),"</s><s>'",X),"</s></t>"),"//s"),"'","")))

If VBA is a must, you could think about a regular expression to replace all the characters from a certain class with a uniform delimiter to use Split() on:

Sub Test()

Dim s As String: s = "CD!02?WX12EF"
Dim arr() As String

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "[!§$%&\/()=?`*'_:;°,.#+´ß}\][{³²^-]"
    arr = Split(.Replace(s, "!"), "!")
End With

For Each el In arr
    Debug.Print el
Next

End Sub

The caret has been moved from being the 1st character to any but the first to prevent a negated-character class; also the hyphen has been moved to the back to prevent an array-construct of characters. Funny enough, if you actually want to be less verbose you could throw these character in a more condense class [!#-/:;?[]-`{}§=°´ß³²].

Upvotes: 1

Related Questions