String Split in vba

nojy · Jul 26th 2005

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

**Batman** · Jul 26th 2005

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.

nojy · Jul 26th 2005

Re: String Split in vba

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

Thanks.

**Andy Pope** · Jul 26th 2005

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]

nojy · Jul 26th 2005

Re: String Split in vba

works great thanks!

Whiteroom · Dec 27th 2010

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

cytop · Dec 27th 2010

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.

AAE · Dec 27th 2010

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

String Split in vba

Participate now!

Share