Split Function

U have a string of values seperated by a delimiter for ex.

One,Two,Three...

Split will return an array of these values when u provide the string and the seperator. An example follows..

Dim strValues As String
Dim arrValues() As String

strValues = "One,Two,Three"
arrValues = Split(strValues, ",")

Split takes a string and separates it by the delimeter of your choosing and puts it into a zero-based array where they are all string types.
For Example: you could have:

dim aSplit() as string
str = "Merry Christmas to all and to all a good-night"
aSplit=split(str," ") ' split the string by space
so the elements of the array would be:
aSplit(0) = "Merry"
aSplit(1) = "Christmas"
aSplit(2) = "to"
aSplit(3) = "all"
aSplit(4) = "and"
aSplit(5) = "to"
aSplit(6) = "all"
aSplit(7) = "a"
aSplit(8) = "good-night"

Now let's say you wanted to split that by something else.  Let's split the string by the hyphen (-).
str = "Merry Christmas to all and to all a good-night"
aSplit=split(str,"-") ' split the string by a hyphen
so the elements of the array would be:
aSplit(0) = "Merry Christmas to all and to all a good"
aSplit(1) = "night"

There's also a comparable function called Join.  This takes an array of strings and puts them back into a single string where you specify the delimeter to put into it.
so in our first example, if we wanted to reassemble the string, we could perform:
str = join(aSplit," ") ' reassemble the string with spaces

and in our second example we would use:
str = join(aSplit,"-") ' reassemble the string with hyphens

Frequently Split is used to parse text files.  If you get the content of the whole text file by reading it all in at once, you can split the individual lines on the vbCRLF delimeter.
So this Text File (delimeted by asterisks so you can see where I'm going)
****************
Merry Christmas
To all and to all
a good night
****************
can be split into it's individual lines by:

dim aSplit() as string
str = TotalFileLines
aSplit=split(str,vbcrlf) ' split the string by carriage return and line feed
so the elements of the array would be:
aSplit(0) = "Merry Christmas"
aSplit(1) = "to all and to all"
aSplit(2) = "a good night"

Now you can cycle through the array and perform operations on each line individually

hi asci,

You have gotten some super good comments to this question.
I copied some to paste in my personal help file.

The easy solution would be to upgrade to VB6..
At some point you must upgrade. You could point out to your company that the cost of the hours spent doing a work-around would be more than the cost of an upgrade.

I have Excel XP which has VBA with the Split function.
I think Excel 2000 would also have it, but I do not know about Excel 97. My point is that if you have a newer version of Excel then you could use VB5 to invoke Excel and use the Excel Split function.

Good luck and Merry Christmas and Happy 2003 ...leo

Hi,

Mind me jumping in, but there is a real good site on the internet where functions like Split, Join and other native VB6 methods are also made available for VB5. They are the fastest around, sometimes even faster then the VB6 native functions. Some are not realy usefull since they introduce TLB's or other things.

The addy is http://www.xbeat.net/vbspeed/c_Split.htm that takes you right to the split function for VB5. Enjoy, I find it a usefull site. I just wanted to share it.

Grtz.©

D.

leojl---you are correct, All VBA 2000+ applications (Access/Excel/Word) have the Split Function (VBA 2000 + is based on VB6, while VBA 97 was based on VB 5).

Arthur Wood

Hey Askee,

Are you reading any of this stuff?? If so give your comments. Its you who we are trying to help after all

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
- answered by Drifter88zxtW (gave how to write in VB5)
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

twalgrave
Cleanup Volunteer

per recommendation

SpideyMod
Community Support Moderator @Experts Exchange