VB String Array Functions - Split, Join, Filter

Level:
Level2

$ReqTestHarness$

VB 6 introduced a trio of powerful functions that operate on string arrays. These functions save the coding effort of having to set up loops and using combinations of other basic string functions to perform the equivalent tasks.

The string array functions are:

Function	Description
Split	Splits a string into separate elements based on a delimiter (such as a comma or space) and stores the resulting elements in a zero-based array
Join	Joins (concatenates) elements of an array into an output string
Filter	Returns a zero-based array containing subset of a string array based on a specified filter criteria.

The MSDN definitions for these functions follow:

Function:

Split

Description:

Returns a zero-based, one-dimensional array containing a specified number of substrings.

Syntax:

Split(expression[, delimiter[, count[, compare]]])

The Split function syntax has these parts:

Part	Description
expression	Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.
delimiter	Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.
count	Optional. Number of substrings to be returned; â€“1 (the default) indicates that all substrings are returned.
compare	Optional. Numeric value indicating the kind of comparison to use when evaluating substrings (0 = case sensitive, 1 = case-insensitive). Built-in "vb" constants can be used for the compare argument: vbBinaryCompare for 0 (case-sensitive search) vbTextCompare for 1 (case-insensitive search)

Function:

Join

Description:

Returns a string created by joining a number of substrings contained in an array.

Syntax:

Join(list[, delimiter])

The Join function syntax has these parts:

Part	Description
list	Required. One-dimensional array containing substrings to be joined.
delimiter	Optional. String character used to separate the substrings in the returned string. If omitted, the space character (" ") is used. If delimiter is a zero-length string (""), all items in the list are concatenated with no delimiters.

Function:

Filter

Description:

Returns a zero-based array containing subset of a string array based on a specified filter criteria.

Syntax:

Filter(InputStrings, Value[, Include[, Compare]])

The Filter function syntax has these parts:

Part	Description
InputStrings	Required. One-dimensional array of strings to be searched.
Value	Required. String to search for.
Include	Optional. Boolean value indicating whether to return substrings that include or exclude Value. If Include is True, Filter returns the subset of the array that contains Value as a substring. If Include is False, Filter returns the subset of the array that does not contain Value as a substring.
Compare	Optional. Numeric value indicating the kind of comparison to use (0 = case sensitive, 1 = case-insensitive). Built-in "vb" constants can be used for the compare argument: vbBinaryCompare for 0 (case-sensitive search) vbTextCompare for 1 (case-insensitive search)

Remarks:

If no matches of Value are found within InputStrings, Filter returns an empty array. An error occurs if InputStrings is Null or is not a one-dimensional array.

The array returned by the Filter function contains only enough elements to contain the number of matched items.

A "Try It" example has been set up to demonstrate how these three functions might be used.

Suppose you were given an input string of comma-delimited names, such as:

Abby,Bubba,Charlie,Debbie,Edgar

and you wanted to "weed out" only the names that contained a double "b" ("bb") and output the results as a similar comma-delimited string:

Abby,Bubba,Debbie

The "Try It" code to accomplish this is shown below:

Private Sub cmdTryIt_Click()

Dim strInputString As String

Dim strFilterText As String

Dim astrSplitItems() As String

Dim astrFilteredItems() As String

Dim strFilteredString As String

Dim intX As Integer

strInputString = InputBox("Enter a comma-delimited string of items:", _

"String Array Functions")

strFilterText = InputBox("Enter Filter:", "String Array Functions")

Print "Original Input String: "; strInputString

Print "Split Items:"

astrSplitItems = Split(strInputString, ",")

For intX = 0 To UBound(astrSplitItems)

Print "Item("; intX; "): "; astrSplitItems(intX)

Print "Filtered Items (using '"; strFilterText; "'):"

astrFilteredItems = Filter(astrSplitItems, strFilterText, True, vbTextCompare)

For intX = 0 To UBound(astrFilteredItems)

Print "Item("; intX; "): "; astrFilteredItems(intX)

strFilteredString = Join(astrFilteredItems, ",")

Print "Filtered Output String: "; strFilteredString

End Sub

When the program is run, respond to the first prompt as follows:

Respond to the second prompt as follows:

The following output results:

Let us analyze the "Try It" code to explain how this works.

First, the necessary variables are declared. Note that the presence of an empty pair of parentheses following "astrSplitItems" and "astrFilteredItems" declares these items as dynamic arrays:

Dim strInputString As String

Dim strFilterText As String

Dim astrSplitItems() As String

Dim astrFilteredItems() As String

Dim strFilteredString As String

Dim intX As Integer

Next, we prompt for our input data. The line

strInputString = InputBox("Enter a comma-delimited string of items:", _

"String Array Functions")

causes the comma-delimited string we entered ("Abby,Bubba,Charlie,Debbie,Edgar") to be stored in the variable "strInputString".

The line

strFilterText = InputBox("Enter Filter:", "String Array Functions")

caused the "bb" filter we entered to be stored in the variable "strFilterText".

Next, we simply print out the string that was input:

Print "Original Input String: "; strInputString

Then it gets interesting in the next segment:

Print "Split Items:"

astrSplitItems = Split(strInputString, ",")

For intX = 0 To UBound(astrSplitItems)

Print "Item("; intX; "): "; astrSplitItems(intX)

In the segment above, the line

astrSplitItems = Split(strInputString, ",")

causes the five names we entered ("Abby,Bubba,Charlie,Debbie,Edgar") to be stored in separate elements of the "astrSplitItems" dynamic array, indexed from 0 to 4 (i.e., astrSplitItems(0) will contain "Abby" while astrSplitItems(4) will contain "Edgar").

The For/Next loop in the segment displays the array contents so we can verify the results of the Split function:

For intX = 0 To UBound(astrSplitItems)

Print "Item("; intX; "): "; astrSplitItems(intX)

The filtering occurs in the next segment:

Print "Filtered Items (using '"; strFilterText; "'):"

astrFilteredItems = Filter(astrSplitItems, strFilterText, True, vbTextCompare)

For intX = 0 To UBound(astrFilteredItems)

Print "Item("; intX; "): "; astrFilteredItems(intX)

In the segment above, the line

astrFilteredItems = Filter(astrSplitItems, strFilterText, True, vbTextCompare)

tells the Filter function to take the array of five names (astrSplitItems), go thru and apply the filter criteria to it (the "bb" that is contained in the strFilterText variable), and place the results of the filtering in the "astrFilteredItems" dynamic array. In this particular case, three names matched the filter (Abby, Bubba, and Debbie), so those three names were stored in indexes 0 to 2 of the astrFilteredItems array.

The For/Next loop in the segment displays the filtered array contents so we can verify the results of the Filter function:

For intX = 0 To UBound(astrFilteredItems)

Print "Item("; intX; "): "; astrFilteredItems(intX)

In the last lines of the "Try It" code, the line

strFilteredString = Join(astrFilteredItems, ",")

uses the Join function to create one string that is the result of concatenating all elements of the astrFilteredItems array, separating each item with a comma.

The line

Print "Filtered Output String: "; strFilteredString

shows the resulting "joined" string.

Download the VB project code for the example above here.

Wed, 07/18/2012 - 07:42 — newbie

Get String After ":" and Before Comma

hai Guys,

Please Help me,

How to get string only "HOW" From Textbox.

text1.text= "KNOW": HOW,"WHAT","GET"

I just want to take character After ":" and Before Char ","
that char is HOW

Thanks Advance..

Sat, 07/07/2012 - 06:33 — Vinay Kumar Soni (not verified)

Thank U Very Much

I was use Mid, Left, InStr etc. for split full Name into first Name, Middle Name , Last name.
It is 100% suitable solution for solving my problem.

Thanks a lot!

Tue, 02/14/2012 - 21:55 — punkz (not verified)

HELP! I'm getting 'Type Mismatch Error' in the Split code

i am doing a project on making student grades, but when i run the program and select a name from the listview, i always get the error 'Type Mismatch', and it seemed that the split code is the one responsible for my problem

here is the line of the code:

Set popGrade = cn.Execute("SELECT * FROM Master_Record WHERE studentID = '" + frmAllStudent.lstAllStudents.SelectedItem.SubItems(1) + "'" + _
" and Grade_lvl like '" + gradeL + "'")
 
    With popGrade
        While Not .EOF
            If !year_done <> 0 And GradeFormTab.TabEnabled(1) = False Then
                If !year_done = 1 Then
                    GradeFormTab.TabEnabled(1) = True
                End If
 
                    gra = Split(popGrade!Final_Grade, "|")
                    act = Split(popGrade!Action_Taken, "|")
                    grading = Split(popGrade!grading, "|")
                    grades = Split(popGrade!grades, "|")

pls help me, i need it so badly, ASAP

Sat, 10/15/2011 - 23:22 — Ray (not verified)

Format numbers with thousand commas

I'm reviewing VB code that checks tables and formats values with a thousand comma separator where the number is prefixed by "[,]". Some cells result in only the "[,]" code, in which case the macro inserts a hard return. Can you help me to change the code so that it just removes the code "[,]" rather than inserting the hard return, which I understand is "Chr(13)"?
The code follows:
'cycle through all the tables in the document
For Each mytable In ActiveDocument.Tables
mytable.Select
intCells = Selection.Cells.Count

'cycle through all the cells in the selected table
For intX = 1 To intCells
'look for cells starting with a dollar sign
If Left(Trim(Selection.Cells(intX)), 1) = "$" Or Left(Trim(Selection.Cells(intX)), 3) = "[,]" Then
blnNoDollar = False
'drop dollar sign at front
If Left(Trim(Selection.Cells(intX)), 1) = "$" Then
strCellText = Mid$(Trim(Selection.Cells(intX).Range.Text), 2, Len(Selection.Cells(intX).Range.Text) - 1)
Else
blnNoDollar = True
Selection.Cells(intX).Range.Text = Right(Selection.Cells(intX).Range.Text, Len(Selection.Cells(intX).Range.Text) - 3)
Selection.Tables(1).Select
strCellText = Mid$(Trim(Selection.Cells(intX).Range.Text), 1, Len(Selection.Cells(intX).Range.Text) - 1)
End If

'get rid of invalid characters at end
Do Until Right(strCellText, 1) <> Chr(13) And Right(strCellText, 1) <> Chr(7)
strCellText = Left(strCellText, Len(strCellText) - 1)
Loop

'loop through cell contents and separate any text characters at the end of the cell from the
'rest of the cell ie DR/CR etc

'Once this is done, format the number and replace the cell contents with the newly formatted
'number, adding any text back to the end of the cell.

For intY = (Len(strCellText)) To 1 Step -1
If IsNumeric(Mid$(strCellText, intY, 1)) Then
strCellTextEnd = Right(strCellText, Len(strCellText) - intY)
If blnNoDollar = False Then
strCellText = Format(Left(Trim(strCellText), intY), "$#,##0.00")
Else
strCellText = Format(Left(Trim(strCellText), intY), "#,##0.00")
End If

Selection.Cells(intX).Range.Text = strCellText & strCellTextEnd
mytable.Select
Exit For
End If
Next intY
End If
Next intX
Next mytable
End Sub

Thu, 02/24/2011 - 10:00 — Dennis Mundo (not verified)

Hello Good day!! May i ask

Hello Good day!!

May i ask how can i compute the time... for example.. between 12:00am to 12:30am... then i prepared text box in which the answer will appear on that text box... can i have some code on vb? thanks.

Fri, 10/29/2010 - 07:31 — Anonymous (not verified)

Serching data with Listview tool

I have make a program with using List View tool. And i want search data as just type any string in text box then search the data in List view. Just like as google search. plz. help with code.

Fri, 10/15/2010 - 07:21 — nazeer (not verified)

This code will extract the filename from a path

Option Explicit
Dim lngIndex
Dim lngStrLen
Dim strFullPath
Dim strFileName
'This code will extract the filename from a path
strFullPath = "C:\Windows\Temp\Test\myfile.txt"
lngStrLen = Len(strFullPath)
For lngIndex = lngStrLen To 1 Step -1
If Mid(strFullPath, lngIndex, 1) = "\" Then
strFileName = Right(strFullPath, lngStrLen - lngIndex)
Exit For
End If
Next
MsgBox "The filename is: " & strFileName

Fri, 10/15/2010 - 07:15 — NAZEER (not verified)

GET NUMARIC AND STRING VALUES SEPERATELY

Dim oarray()
strSize=array("S","M","L","XL",98,87)
For k = 0 to ubound(strSize)
va=strSize(k)
If VarType(va) = 8 Then
'msgbox "string"
sva=sva&va
else
If VarType(va) = 2 Then
'msgbox "numaric"
rsva=rsva&va
End if
End if
next
msgbox sva
msgbox rsva

Fri, 10/15/2010 - 07:12 — NAZEER (not verified)

GET NUMARIC AND STRING VALUES SEPERATELY

Thu, 07/22/2010 - 01:14 — ubaid (not verified)

search question?

I am a student, I am making a database sofware in which I want to search a value or string in a Datagrid, for example: when I write a to search so all the words which begings with a should appear in Datagrid and when I write ab to search so all the words which begins with ab should appear in the Datagrid.
I am using a text box for taking input for search & searching a database field in the DataGrid.
Please Help Me.

Thank You so much!

Thu, 12/09/2010 - 03:20 — Anonymous (not verified)

help ui

u just need to put the code in (textbox name)_change then let to display at ur grid..

Sun, 07/04/2010 - 01:59 — Anonymous (not verified)

My program is lifting text

My program is lifting text from cells in an excel sheet (whose range I have specified) and displaying them in a list using vba.
problem is that all of the cells being quoted are being done in one huge sentence form with no spaces. If the text is in the range specified but from a different cell I want there to be a space.
how do I insert the space ?!

I have defined each array, for example Test(12) as array and then specified what the range for Test(1), Test(2) and so on is. I have then simply added teh code.
listbox1.additem Test(1)
listbox1. add item Test(2)
and so on...

but the list gets populated as one HUGE sentence.

Sat, 04/24/2010 - 05:54 — Anonymous (not verified)

awesome

Mon, 02/08/2010 - 08:22 — Anonymous (not verified)

Help.!

Private Sub Command1_Click()
Dim Total_Time As Integer
Dim NumberOf_Tracks As Integer
Dim Track_Length(20) As Integer
Dim Track_Title(20) As String

'Step 1'
Total_Time = 0

'Step 2'
Do
NumberOf_Tracks = InputBox("Please enter Number of Tracks.")
If NumberOf_Tracks > 20 Then
MsgBox ("Too Many Tracks.")
End If
Loop Until NumberOf_Tracks >= 1 And NumberOf_Tracks <= 20

'Step 3'
For Counter = 1 To NumberOf_Tracks

'Step 4'
Track_Title(20) = InputBox("Enter Track Title.")
Track_Length(20) = InputBox("Enter Track Length (Seconds).")

'Step 5'
Total_Time = Total_Time + Track_Length(20)

'Step 6'
Next Counter

'Step 7'
For Counter = 1 To NumberOf_Tracks
Form1.Print Track_Title(20); Track_Length(20); ("Seconds")
Next Counter

'Step 8'
Form1.Print ("CD-R Running Time -"); Total_Time

Can anyone help me display the Array's properly. Please help me with the Array's. :{

Thu, 02/04/2010 - 05:00 — Anonymous (not verified)

Hey, I have a huge string

Hey, I have a huge string seperated by several ",". So far no problem with the split function. But now I only want to perform a split after every second ",". Anyone an idea how that is possible?

Thu, 03/18/2010 - 07:15 — Anonymous (not verified)

Have you tried using a

Have you tried using a counter of the delimiters, and based on which ones you are trying to delimit by, every 1st and 3rd, or 2nd and 4th, execute the code by that? Or something to that effect.

Tue, 09/22/2009 - 02:23 — Jeet Rai (not verified)

Well Done !!

asf Simply the best. Good explanaiton with example.

Wed, 08/26/2009 - 15:13 — Anonymous (not verified)

Code doesn't work. Great!

Mon, 08/17/2009 - 22:24 — Krishna (not verified)

Can We pass Array variable as Parameter for Select Case?

]
ProdName =Split(X,"-") 'some thing with split 
Select case ProdName(0) 'Can we pass like this
Case 872999,874999,875199,875299,875699,875899,879699,880799,885299,886299,886499,889499,892599
	amount1= GetXMLValue(strFileName, "paymentAmount", "amount","1")
	msgbox amount1
Case "CDIT","LCDXTranche","cds999","cds99901"
	amount1 =GetXMLValue(strFileName,"paymentAmount","amount","1")
	msgbox amount1
Case "cmbx"
	amount1 =GetXMLValue(strFileName,"paymentAmount","amount","2")
	msgbox amount1
End Select

Im getting "type mismatch error" if ProdName(0) is 'String' ,But it works fine if ProdName(0) is 'Number' ie first case passes.Can you please suggest.

Thu, 06/18/2009 - 15:54 — Emmanuel C. (not verified)

Thanks Guys !

This is great. I have been struggling to get this split function to work on my project for days and I am so happy that I found this site. It has been very helpful.
Keep on my the good work. It's just so great to have access to this kind of material while studying.
Cheers!

Sun, 02/14/2010 - 19:56 — rachel ramos (not verified)

visual basic

plz............... help me in the program

Tue, 06/09/2009 - 03:20 — Prashant Bapat (not verified)

function query

Split(Item, " , ", -1, 1)

Please Explain me the above instruction and what is the use of -1,1 co-ordinates in above instruction.

Tue, 04/21/2009 - 00:39 — Carlos Joao (not verified)

This is very helpfull but

This is very helpfull but what if I want to separate each charecter of a word into an array using the split function? Can anyone help me with that, I have been trying to do this for days now and I cant get a way of doing it. But I am using visual basic.net.

eg I want to separate the charecters of word "HELLO" into an array that would look like this:

array(0)="H"
array(1)="E"
array(2)="L"
array(3)="L"
array(4)="O"

Please help me to get this right...
Thanks

Sun, 07/31/2011 - 11:53 — Anonymous (not verified)

You can use this fonction :

Dim array() As String
Sub split(text As String)
a = Len(text)
ReDim array(a)
For b = 1 To a
array(b) = Mid$(text, b, 1)
Next b
End Sub

Then call this fonction:

Private Sub Command1_Click()
split "HELLO"
End Sub

Mon, 12/28/2009 - 11:26 — Anonymous (not verified)

Use Mid string function

'use mid$ function

sString = "HELLO"
for iCnt = 1 to len(sString)
array(iCnt) = mid(sString,iCnt,1)
next

'Something like that.

Wed, 03/18/2009 - 03:22 — Anonymous (not verified)

Gr8

Wed, 02/04/2009 - 06:01 — Roger (not verified)

Well trough your example i

Well trough your example i can manage to find my solution, but im still confused.
Im trying to split every word that an Input Box will have, but i need to save all the words into Array variables, i'm creating a Tag Cloud in Visual Basic 6.0.
Can you help me out with that? Cant figure out how to use Split function

Wed, 01/21/2009 - 20:39 — Anonymous (not verified)

vb6

can you just send me a vb6 that operate a selection of "menu" then the payment received, item to be paid, and change of the costumer...
please i need this as my project...

Tue, 11/04/2008 - 23:44 — xanne0037 (not verified)

euricka!

this page satisfies my need, this' what i am looking for!

i think, not so soon my grades will be completed!

thanks!

Wed, 10/29/2008 - 05:55 — Fubzot (not verified)

Thanx

Just spent 1 hr making a splitter when I stumble upon this post telling me that it is already included....

*sigh*

Tue, 10/07/2008 - 23:52 — sreenivas (not verified)

Navigate To

Guides

User login

VB String Array Functions - Split, Join, Filter

Function

Description

Split

Splits a string into separate elements based on a delimiter (such as a comma or space) and stores the resulting elements in a zero-based array

Join

Joins (concatenates) elements of an array into an output string

Filter

Post new comment