Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

String Split in vba

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: String Split in vba

    Jon,

    Try the following
    Code:
    Sub SplitValue()
        Dim avarSplit As Variant
        
        avarSplit = Split(Range("A1").Value, ",")
    End Sub
    Hope this helps.

    Regards,
    Batman.
    Regards,
    Batman.

    Comment


    • #3
      Re: String Split in vba

      That works a treat thanks, how could I find the length of the variable?

      Thanks.

      Comment


      • #4
        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.
        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

        Cheers
        Andy

        Comment


        • #5
          Re: String Split in vba

          works great thanks!

          Comment


          • #6
            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

            Comment


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

              Comment


              • #8
                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).
                AAE
                ----------------------------------------------------

                Forum Rules | Message to Cross Posters | How to use Tags

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X