Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    Here's something back for the help I have received

    To say thank you to all those who have helped me, here is a little routine which will take a string of comma separated values and return the n'th value to the user. Feel free to use it in any way you wish - just keep the acknowledgement in from me is all I ask.

    Have fun - and please report back if you find any errors.

    Alan

    PS - There may well be built in Excel VB macro to do this - but I had fun writing this.

    Code:
    Function SplitID(inputdata, num, error)
    '
    '*** takes a string of comma separated values as input and gives back the n'th one
    '
    '*** "Inputdata" is the string of comma separated values.
    '*** "num" is the item you want to be returned
    '*** "Error" is returned with a 1 if a problem is found
    '
    '*** (c) Alan Sharkey 2014. Please re-use this - I ask for no payment, only recognition
    '
    
    Dim dotpos(1 To 255) As Integer ' since we can only have a maximum of 255 chars in a string
    Dim i, num_commas, fnlen, lastone As Integer
    '
    '*** zero out the array just in case
    '
        For i = 1 To 255
            dotpos(i) = 0
        Next i
        error = 0 ' start off with no errors
    
        lastone = 1
        fnlen = Len(inputdata)
    '
    '*** find all the commas
    '
        For i = 1 To fnlen
            aa = Mid(inputdata, i, 1)
            If aa = "," Then
                dotpos(lastone) = i
                lastone = lastone + 1
            End If
        Next i
    '
    '*** all the locations of the commas are in the array
    '
        num_commas = lastone - 1 ' and this is how many we found
    '
    '*** check we are OK. If num is outside the range of found  then just exit
    '
        If num > lastone Or num < 1 Then
            error = 1
            SplitID = ""
            GoTo end1
        End If
        
        If num_commas = 0 Then ' none found, return the full array
            SplitID = inputdata
            GoTo end1
        End If
        
    '
    '*** now, which do we want out?
    '*** first and last are special cases - the others are all generic
    '
        If num = 1 Then ' first item needed
            SplitID = Left(inputdata, dotpos(1) - 1)
        End If
        
        If num = lastone Then ' last item needed
            SplitID = Mid(inputdata, dotpos(num_commas) + 1, Len(inputdata) - dotpos(num_commas))
        End If
        
        If (num <> 1) And (num <> lastone) Then ' all the ones in the middle.
            SplitID = Mid(inputdata, dotpos(num - 1) + 1, dotpos(num) - dotpos(num - 1) - 1)
        End If
        
    end1:
        
    End Function

  2. The Following User Says Thank You to alan sh For This Useful Post:

    Maudibe (2014-02-26)

  3. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by alan sh View Post
    To say thank you to all those who have helped me, here is a little routine which will take a string of comma separated values and return the n'th value to the user. Feel free to use it in any way you wish - just keep the acknowledgement in from me is all I ask.

    Have fun - and please report back if you find any errors.

    Alan

    PS - There may well be built in Excel VB macro to do this - but I had fun writing this.

    Code:
    Function SplitID(inputdata, num, error)
    '
    '*** takes a string of comma separated values as input and gives back the n'th one
    '
    '*** "Inputdata" is the string of comma separated values.
    '*** "num" is the item you want to be returned
    '*** "Error" is returned with a 1 if a problem is found
    '
    '*** (c) Alan Sharkey 2014. Please re-use this - I ask for no payment, only recognition
    '
    
    Dim dotpos(1 To 255) As Integer ' since we can only have a maximum of 255 chars in a string
    Dim i, num_commas, fnlen, lastone As Integer
    '
    '*** zero out the array just in case
    '
        For i = 1 To 255
            dotpos(i) = 0
        Next i
        error = 0 ' start off with no errors
    
        lastone = 1
        fnlen = Len(inputdata)
    '
    '*** find all the commas
    '
        For i = 1 To fnlen
            aa = Mid(inputdata, i, 1)
            If aa = "," Then
                dotpos(lastone) = i
                lastone = lastone + 1
            End If
        Next i
    '
    '*** all the locations of the commas are in the array
    '
        num_commas = lastone - 1 ' and this is how many we found
    '
    '*** check we are OK. If num is outside the range of found  then just exit
    '
        If num > lastone Or num < 1 Then
            error = 1
            SplitID = ""
            GoTo end1
        End If
        
        If num_commas = 0 Then ' none found, return the full array
            SplitID = inputdata
            GoTo end1
        End If
        
    '
    '*** now, which do we want out?
    '*** first and last are special cases - the others are all generic
    '
        If num = 1 Then ' first item needed
            SplitID = Left(inputdata, dotpos(1) - 1)
        End If
        
        If num = lastone Then ' last item needed
            SplitID = Mid(inputdata, dotpos(num_commas) + 1, Len(inputdata) - dotpos(num_commas))
        End If
        
        If (num <> 1) And (num <> lastone) Then ' all the ones in the middle.
            SplitID = Mid(inputdata, dotpos(num - 1) + 1, dotpos(num) - dotpos(num - 1) - 1)
        End If
        
    end1:
        
    End Function
    Hi Alan, glad you had fun writing this.

    You may want to check out the Split function in VBA. I don't think it was available in earlier versions because I remember having to split strings manually myself, but it is a bit of a lifesaver in Excel 2007 onwards

  4. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    My SplitID function is much more flexible and seems to work better (it can be used in a cell or in a macro, for example). If I was going to add anything, it would be to pass the delimiter across, rather than had coding it as a comma.

    Alan

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    If the values are separated by a comma followed by a space, the resulted value will be preceded by a space. You might want to use the trim method before returning the value. Just wondering why the function would require an error parameter in the call?
    Last edited by Maudibe; 2014-01-18 at 14:29.

  6. #5
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Will this work as well?

    Code:
    Public Function ModifiedSplit(txt As String, num As Integer) As String
    On Error GoTo ErrorMessage
        Dim zString As Variant
        zString = Split(txt, ", ")
        ModifiedSplit = zString(num - 1)
        Exit Function
    ErrorMessage:
        ModifiedSplit = "Either the string is in the wrong format or the number is < " & LBound(zString)+1 & " or > " & UBound(zString) + 1
    End Function
    Last edited by Lexi; 2014-01-18 at 20:33.

  7. #6
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Maudibe,

    Thanks - I'll think about that, However, spaces may be relevant so I think it's up to the caller to trim the value when it's returned rather than assume that spaces should be eliminated.

    Lexi,

    Yes, but the split function doesn't do what mine does - which is to return the n'th item in a set of comma separated values.

    regards

    Alan

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Option Explicit
    but the split function doesn't do what mine does - which is to return the n'th item in a set of comma separated values.
    No, but it could be used in UDF to do it (as Lexi demonstrated). Another example (and adding a optional delimiter variable):
    Code:
    Option Explicit
    Function NthElement(str As String, n As Integer, Optional sDL As String = ",")
      Dim vArray
      vArray = Split(str, sDL)
      NthElement = vArray(n - 1) 'split is 0-based
    End Function
    Call this like (get the 2nd element of the string in A1)
    =NthElement(A1,2)
    or
    =NthElement(A1,2,",")
    if comma separated.

    Or if (for example) semicolon separated:
    =NthElement(A1,2,";")

    The delimiter can even be multiple characters if desired. I did nto add the error functionality, the function will display an error if there is one. If you want to return some error text, then it would be good to define it. I would prefer an actual error returned than a text message.

    Steve
    Last edited by sdckapr; 2014-01-20 at 09:14.

  9. #8
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Alan,

    I believe it does show the nth item. That is what the parameter num is for and the line ModifiedSplit = zString(num - 1) returns the nth item of the zString array.

    Lexi

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Alan sh,

    I agree, the space may be very significant especially if the returned value is used in additional coding and thus really should be removed. This can be easily done by adding the LTrim function to your return statements.

    Code:
        If num = 1 Then ' first item needed
            SplitID = LTrim(Left(inputdata, dotpos(1) - 1))
        End If
        
        If num = lastone Then ' last item needed
            SplitID = LTrim(Mid(inputdata, dotpos(num_commas) + 1, Len(inputdata) - dotpos(num_commas)))
        End If
        
        If (num <> 1) And (num <> lastone) Then ' all the ones in the middle.
            SplitID = LTrim(Mid(inputdata, dotpos(num - 1) + 1, dotpos(num) - dotpos(num - 1) - 1))
        End If
    I would also suggest taking out the error parameter as it doesn't serve any function. It is confusing to the user because there is no clue as what to input for the parameter in the function call and your code immediately sets it to zero anyway. You will need to comment out the 2 error statements as well in the code.

    Alan, we all have our code tweaked by each other and you should really not be offended by constructive criticism. You should be proud of your accomplishment as you probably figured out the code behind the code for the Split function....something we all take for granted.

    Maud

  11. #10
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Thanks all for the comments - I'm not at all offended. I'm really happy with the banter back and forth and I've learned new things as well. I'll update my code just for fun, but I will also look at the Split function which I didn't even know existed.

    As to the error part of my code. I use that to determine whether someone entered a stupid value. Maybe it needs better commenting, but I think it should stay.

    Cheers

    Alan

Posting Permissions

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