String Split in vba

  • Hi,


    I've looked at several threads on the forum relating to spliting a string, however there not quite answering the problem I have:


    Within VBA I have a string that is actually taken from a cell, this could be any length and is a list of usernames seperated by a coma.


    What I need to be able to do is split this string into an array, with each user name appearing in a different element of the array, i.e:


    OrginalString = Fred,Smith


    Split down to:


    String(0) = Fred
    String(1) = Smith


    Does this make sense, any suggestions would be greatly recieved,


    Thanks


    Jon Hardman

  • Re: String Split in vba


    Hi,


    Here is an expansion of Batman's code which reports the length of each string that has been split.
    [vba]Sub SplitValue()
    Dim avarSplit As Variant
    Dim intIndex As Integer
    avarSplit = Split(Range("A1").Value, ",")
    For intIndex = LBound(avarSplit) To UBound(avarSplit)
    MsgBox "Item " & intIndex & " is " & avarSplit(intIndex) & _
    " which is " & Len(avarSplit(intIndex)) & " characters long", vbInformation
    Next
    End Sub[/vba]

  • Re: String Split in vba


    Sorry to resurrect an old thread but this seems to be what i need but i cant seem to get it to work.


    I have a cell that can have upto 10 figures in it.


    They are in the format of 40, 31, 20


    Ive used the code stated here but all i get is the 40. I would like to be able to split the string and add the numbers together. This is so i can get a total and an average value from them. Any ideas?


    at the moment my code is


    Quote

    avarSplit = Split(Range("I8").Value, ",")
    Cells(1, 1) = avarSplit

  • Re: String Split in vba


    Expect a rap on the knuckles from a moderator. This is one of the rules you agreed to when joining...

    Quote


    8. Never post a question in the Thread of another member. You MUST ALWAYS start you own New Thread


    Your problem is simple to resolve, but you won't get an answer until you start your on thread.

  • Re: String Split in vba


    Hello Whiteroom,


    Welcome to Ozgrid. While we're glad to have you in the community, please take some time to read the rules you agreed to follow.


    Posting your question in threads started by others is a violation of the forum rules and is known as thread hijacking. Posting solutions is acceptable.
    ALWAYS start a new thread for your question and if you think it is helpful to clarify your thread you may include a link back this thread (or any other).