Consulting

Results 1 to 14 of 14

Thread: Help with Split Function

  1. #1

    Help with Split Function

    The code below will return the output:

    red 10-16
    aconite 13-17
    suberect spatholobus stem 5-15
    tianxiong 12-18
    ligusticum striatum 2-4
    celastreae 25-35
    maca 10-18
    eucommia bark 18-22
    fennel 10-20
    radix paeoniae alba 3-5
    raw rheum officinale 10-18
    honeysuckle 10-20
    rhizoma arisaematis 10-20
    radix astragali 4-8
    talc 5-16
    asarum 2-4
    notopterygium root 16-20
    dried orange peel 8-25
    radix codonopsitis 2-6
    root of kudzu vine 10-20
    pangolin 17-23
    rehmannia glutinosa 8-25
    ground beeltle 11-15
    semen brassicae 5-15
    rhododendron molle 2-4
    distilled spirit 200-300


    How do I change the code so that the output is formatted this way:

    10-16 pts wt. red
    13-17 pts wt. aconite
    5-15 pts wt. suberect spatholobus stem
    12-18 pts wt. tianxiong
    2-4 pts wt. ligusticum striatum
    10-18 pts wt. celastreae
    25-35 pts wt. maca
    18-22 pts wt. eucommia bark
    10-20 pts wt. fennel
    3-5 pts wt. radix paeoniae alba
    10-18 pts wt. raw rheum officinale
    10-20 pts wt. honeysuckle
    10-20 pts wt. rhizoma arisaematis
    4-8 pts wt. radix astragali
    5-16 pts wt. talc
    2-4 pts wt. asarum
    16-20 pts wt. notopterygium root
    8-25 pts wt. dried orange peel
    2-6 pts wt. radix codonopsitis
    10-20 pts wt. root of kudzu vine
    17-23 pts wt. pangolin
    8-25 pts wt. rehmannia glutinosa
    11-15 pts wt. ground beeltle
    5-15 pts wt. semen brassicae
    2-4 pts wt. rhododendron molle
    200-300 pts wt. distilled spirit

    Sub Test()
    Dim str As String
    str = "red, 10-16 parts of aconite, 13-17 parts of suberect spatholobus stem, 5-15 parts of tianxiong, 12-18 parts ligusticum striatum, _
     2-4 parts celastreae, 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 astragali, _
     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"
    str = Replace(str, ",", "")
    str = Replace(str, "parts of ", "pts wt.,")
    str = Replace(str, "parts ", "pts wt.,")
    str = Replace(str, "shares of ", "pts wt.,")
    str = Replace(str, "shares ", "pts wt.,")
    str = Replace(str, "shares", "pts wt.,")
    Dim a As Variant
    Dim b As Variant
    a = Split(str, "pts wt.,")
    b = UBound(a)
    For i = 0 To b
         Debug.Print a(i)
    Next i
    End Sub
    Last edited by Aussiebear; 04-14-2023 at 02:30 AM. Reason: Adjusted the code tags

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
       sn = Split("raw rheum officinale 10-18")
       c00 = Replace(Filter(sn, "-")(0), "-", " - ") & " pts. ws. " & Join(Filter(sn, "-", 0))
    End sub

  3. #3
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    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)

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Nearly:
    Sub Test()
    myStr = "red, 10-16 parts of aconite, 13-17 parts of suberect spatholobus stem, 5-15 parts of tianxiong, 12-18 parts ligusticum striatum, 2-4 parts celastreae, 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 astragali, 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"
    
    myStr = Replace(myStr, "parts of ", "¬")
    myStr = Replace(myStr, "parts ", "¬")
    myStr = Replace(myStr, "parts", "¬")
    myStr = Replace(myStr, "shares of ", "¬")
    myStr = Replace(myStr, "shares ", "¬")
    myStr = Replace(myStr, "shares", "¬")
    a = Split(myStr, "¬")
       
    b = UBound(a)
    On Error Resume Next
    For i = 0 To b
      Z = Split(a(i), ",")
      Debug.Print Application.Trim(Join(Array(Z(1), "pts wt.", Z(0))))
    Next i
    End Sub
    To a naive eye, I would have thought more like:
    Sub test2()
    myStr = "red, 10-16 parts of aconite, 13-17 parts of suberect spatholobus stem, 5-15 parts of tianxiong, 12-18 parts ligusticum striatum, 2-4 parts celastreae, 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 astragali, 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"
    myStr = Replace(myStr, "parts of ", "¬")
    myStr = Replace(myStr, "parts ", "¬")
    myStr = Replace(myStr, "parts", "¬")
    myStr = Replace(myStr, "shares of ", "¬")
    myStr = Replace(myStr, "shares ", "¬")
    myStr = Replace(myStr, "shares", "¬")
    a = Split(myStr, ",")
    For i = LBound(a) To UBound(a)
      Debug.Print Application.Trim(Replace(a(i), "¬", " pts wt. "))
    Next i
    End Sub
    Last edited by p45cal; 10-12-2018 at 07:39 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    wow thanks a lot guys

  6. #6
    Quick question guys: How do I change the code so that output would not be from debug.print but to activesheet.textbox7.text

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Change
    Debug.print
    To
    activesheet.textbox7.text =
    Last edited by Aussiebear; 04-14-2023 at 02:34 AM. Reason: Added code tags
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    I did that but only the last line is copied.

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    What do you want in the text box? One long string? A vertical list... that you can choose from? Is it a text box you want, or something else?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Did you overlook #2 ?

  11. #11

    debug.print to textbox

    I need the output in one long list separated by comma.

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Sub Test()
    myStr = "red, 10-16 parts of aconite, 13-17 parts of suberect spatholobus stem, 5-15 parts of tianxiong, 12-18 parts ligusticum striatum, 2-4 parts celastreae, 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 astragali, 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"
    
    myStr = Replace(myStr, "parts of ", "¬")
    myStr = Replace(myStr, "parts ", "¬")
    myStr = Replace(myStr, "parts", "¬")
    myStr = Replace(myStr, "shares of ", "¬")
    myStr = Replace(myStr, "shares ", "¬")
    myStr = Replace(myStr, "shares", "¬")
    a = Split(myStr, "¬")
       
    b = UBound(a)
    On Error Resume Next
    For i = 0 To b
      Z = Split(a(i), ",")
      'Debug.Print Application.Trim(Join(Array(Z(1), "pts wt.", Z(0))))
      ThisStr = Application.Trim(Join(Array(Z(1), "pts wt.", Z(0))))
      If YourStr = "" Then YourStr = ThisStr Else YourStr = Join(Array(YourStr, ThisStr, ","))
    Next i
    ActiveSheet.textbox7.Text = YourStr
    End Sub
    You can do similar with others' suggestions.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
       sn = Split(Replace("red, 10-16 parts of aconite, 13-17 parts of suberect spatholobus stem, 5-15 parts of tianxiong, 12-18 parts ligusticum striatum, 2-4 parts celastreae, 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 astragali, 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", " of ", " "), ", ")
    
       For j = 0 To UBound(sn) - 1
          sn(j) = Split(sn(j + 1))(0) & " pts wt. " & Join(Filter(Filter(Split(sn(j)), "-", 0), "part", 0))
       Next
       MsgBox Join(sn, ",")
    End Sub

  14. #14
    P45cal and snb: You just made my day. Thanks a lot guys!

Posting Permissions

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