Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: String Split in vba

  1. #1
    Join Date
    19th October 2004
    Posts
    57

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,597

    Re: String Split in vba

    Jon,

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

    Regards,
    Batman.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th October 2004
    Posts
    57

    Re: String Split in vba

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

    Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

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


  5. #5
    Join Date
    19th October 2004
    Posts
    57

    Re: String Split in vba

    works great thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th August 2009
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    1st September 2010
    Posts
    7,607

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Split String At First Delimiter Only
    By MaxTO in forum EXCEL HELP
    Replies: 7
    Last Post: November 11th, 2008, 10:12
  2. Transpose And Split String Into An Array
    By jsolo in forum EXCEL HELP
    Replies: 2
    Last Post: June 8th, 2007, 02:43
  3. Split vbCrLf String
    By jonny in forum EXCEL HELP
    Replies: 4
    Last Post: January 3rd, 2007, 21:19
  4. Split string into different strings
    By FreddyKrueger in forum EXCEL HELP
    Replies: 1
    Last Post: November 16th, 2005, 20:43
  5. Split RTD string to Array?
    By Håkan in forum EXCEL HELP
    Replies: 2
    Last Post: September 22nd, 2004, 23:53

Bookmarks

Posting Permissions

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