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,


    Jon Hardman

  • Re: String Split in vba


    Try the following

    Sub SplitValue()
        Dim avarSplit As Variant
        avarSplit = Split(Range("A1").Value, ",")
    End Sub

    Hope this helps.



  • Re: String Split in vba


    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
    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


    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...


    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).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!