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

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,400
    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
    Ride the wind to the sun
    Posts
    2,588
    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
    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 Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,299
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    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 Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,299
    Change
    Debug.print
    To
    activesheet.textbox7.text =
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    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 Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,299
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    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 Guru
    Joined
    Apr 2012
    Posts
    4,400
    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 Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,299
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    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 Guru
    Joined
    Apr 2012
    Posts
    4,400
    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
  •