Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Analyzing the contents of a string (Word VBA)

    Hi folks, another one of those questions that are hopefully quick and easy to you expert-type folks, but have me flummoxed!

    I'm getting an imported string that consists of between one and several numbers with commas inbetween, eg "23,34,45" or "4".

    I need to (1) count the entries so I know whether to label it "class" (eg "class 4") or "classes" (eg "classes 23, 34 and 45") and (2) reformat the string to include spaces and an "and" where necessary, eg "23, 34 and 45".

    What would be the quickest and easiest way to do this, please?

    Many thanks!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analyzing the contents of a string (Word VBA)

    Let's say that you have a string variable strText. The number of comma-delimited entries in strText is
    <code>
    UBound(Split(strText, ",")) + 1
    </code>
    So you could test for this number being greater than one.

    To insert spaces etc., you can use this function:
    <code>
    Function ConvertString(strText As String) As String
    Dim arrParts() As String
    Dim strRet As String
    Dim i As Integer
    Dim n As Integer
    arrParts = Split(strText, ",")
    n = UBound(arrParts)
    For i = 0 To n
    Select Case i
    Case 0 To n - 2
    strRet = strRet & arrParts(i) & ", "
    Case n - 1
    strRet = strRet & arrParts(i) & " and "
    Case n
    strRet = strRet & arrParts(i)
    End Select
    Next i
    ConvertString = strRet
    End Function
    </code>
    Call it like this:
    <code>
    Dim strText As String
    strText = "John,Paul,George,Ringo"
    strText = ConvertString(strText)
    MsgBox strText
    </code>
    Result: John, Paul, George and Ringo

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Analyzing the contents of a string (Word VBA)

    Hans, as usual you are absolute star! Thank you - that works perfectly!!
    Beryl M


  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Analyzing the contents of a string (Word VBA)

    Hans, is it possible to sort this array numerically? so if the result was "16,42,45,09" sort it to "09,16,42,45"? (before adding the spaces and ' and ' of course!).
    Beryl M


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analyzing the contents of a string (Word VBA)

    The following code will sort the array using a simplistic bubble sort:
    <code>
    Function ConvertString(strText As String) As String
    Dim arrParts() As String
    Dim strRet As String
    Dim i As Integer
    Dim n As Integer
    arrParts = Split(strText, ",")
    ' Sort the array before continuing
    BubbleSort arrParts
    n = UBound(arrParts)
    For i = 0 To n
    Select Case i
    Case 0 To n - 2
    strRet = strRet & arrParts(i) & ", "
    Case n - 1
    strRet = strRet & arrParts(i) & " and "
    Case n
    strRet = strRet & arrParts(i)
    End Select
    Next i
    ConvertString = strRet
    End Function

    Sub BubbleSort(arr)
    Dim i As Integer
    Dim j As Integer
    Dim itm As Variant
    For i = LBound(arr) To UBound(arr) - 1
    For j = i + 1 To UBound(arr)
    If arr(i) > arr(j) Then
    itm = arr(i)
    arr(i) = arr(j)
    arr(j) = itm
    End If
    Next j
    Next i
    End Sub
    </code>
    Bubble sort is not the most efficient sort routine but it is fine for small arrays.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Analyzing the contents of a string (Word VBA)

    Hi Hans, sorry to be a pain, but the thing is the array concerned is not always going to be numbers, and when it is they will be in the form of a string not an integer - as far as I can gather this assumes integers? (at least that's what I gathered when it complained ...!)
    Beryl M


  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Analyzing the contents of a string (Word VBA)

    You could try the old Wordbasic command called SortArray - see http://word.mvps.org/FAQs/MacrosVBA/...icCommands.htm

    Another alternative can be found in this thread http://help.lockergnome.com/office/Problem...pict899760.html
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analyzing the contents of a string (Word VBA)

    My code assumes that the parts are strings (that might contain numbers, or not). Examples:

    ? ConvertString("John,Paul,George,Ringo")
    George, John, Paul and Ringo

    As you see, the names are sorted alphabetically.

    ? ConvertString("John,37,George,15")
    15, 37, George and John

    Numbers are sorted before names.

    > "when it complained ...!"
    Please provide specific details:
    - What was the input string?
    - Where did the error occur?
    - What was the error message?

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Analyzing the contents of a string (Word VBA)

    Okay, it's probably just how I'm using it, then, I'll check. However, if the code is sorting the items as strings, what would it do if it got "25,16,9"? Would it give "9, 16 and 25" (ie treat them as numbers) or "16, 25 and 9" (treating them as text)?
    Beryl M


  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analyzing the contents of a string (Word VBA)

    It would return "16, 25 and 9" because it treats the parts as text. If you want to be able to handle both text and numbers while sorting the numbers correctly, change the BubbleSort procedure to
    <code>
    Sub BubbleSort(arr)
    Dim i As Integer
    Dim j As Integer
    Dim itm As Variant
    For i = LBound(arr) To UBound(arr) - 1
    For j = i + 1 To UBound(arr)
    If IsNumeric(arr(i)) And IsNumeric(arr(j)) Then
    If Val(arr(i)) > Val(arr(j)) Then
    itm = arr(i)
    arr(i) = arr(j)
    arr(j) = itm
    End If
    ElseIf arr(i) > arr(j) Then
    itm = arr(i)
    arr(i) = arr(j)
    arr(j) = itm
    End If
    Next j
    Next i
    End Sub
    </code>
    The ConvertString function can remain the same.

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Analyzing the contents of a string (Word VBA)

    Thanks, Hans, that's perfect! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    I don't know how you do this so quickly and easily but I sure wish I could do it too! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Analyzing the contents of a string (Word VBA)

    Hi Beryl,

    Now all you have to do is work out how to sort strings lexicographically (eg, so that both St Mark and Saint Mark come before Smith).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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