Results 1 to 14 of 14

Thread: Help with Split Function

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #3
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,645
    was working on it with RegEx


    Sub vbax_63844_rearrange_strings()
    Dim toSplit As String
    Dim arrSplit
    Dim i As Long
    toSplit = "red, 10-16 parts of aconite, 13-17 parts of suberect spatholobus stem, 5-15 parts of tianxiong, 12-18 parts ligusticum toSplitiatum, _
     2-4 parts celatoSpliteae, 25-35 parts maca, 10-18 parts of eucommia bark, 18-22 parts of fennel, 10-20 parts of radix paeoniae alba, _
     3-5 parts of raw rheum officinale, 10-18 parts of honeysuckle, 10-20 parts of rhizoma arisaematis, 10-20 parts of radix atoSplitagali, _
     4-8 parts of talc, 5-16 parts of asarum, 2-4 parts of notopterygium root, 16-20 parts of dried orange peel, 8-25 parts of radix codonopsitis, _
     2-6 parts of root of kudzu vine, 10-20 parts of pangolin, 17-23 parts of rehmannia glutinosa, 8-25 parts of ground beeltle, _
     11-15 parts of semen brassicae, 5-15 parts of rhododendron molle 2-4 shares, distilled spirit 200-300 shares"
    toSplit = Replace(Replace(Replace(Replace(Replace(Replace(toSplit, ",", ""), "parts of ", "pts wt.,"), "parts ", "pts wt.,"), "shares of ", _
     "pts wt.,"), "shares ", "pts wt.,"), "shares", "pts wt.,")
    'nest all 6 replaces in 1 line
    arrSplit = Split(toSplit, "pts wt.,")
    On Error Resume Next
    For i = LBound(arrSplit) To UBound(arrSplit)
         With CreateObject("VBScript.RegExp")
              .Pattern = "[0-9]+-[0-9]+"
              .Global = False 'one match only
              .MultiLine = False
              Debug.Print .Execute(arrSplit(i)).Item(0) & " pts. wt. " & .Replace(arrSplit(i), "")
        End With
    Next i
    End Sub
    Last edited by Aussiebear; 04-14-2023 at 02:32 AM. Reason: Adjusted the code tags
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •