All about Visual Basic for Application (VBA) especially for Microsoft Excel.


  • Subscribe

How to use split function using VBA?

Posted by | August 9, 2011 .

How to split text string using Visual Basic for Application in excel? This function is very useful in VBA because sometime we need to split certain text string in piece. For example we have text with comma separated, open close bracket, semi colon and etc. Let’s say you have Text1, Text2, Text3, Text4 separated by comma. In excel if you wish split this text we can use Text to Column under Data tab, select column that contain text to split then follow the wizard. Here you can choose either split using Delimited (Character such as commas or tabs separate each field) or Fixed width (Fields are aligned in columns with spaces between each field). In this case you may choose to use delimited with commas separate and click finish. If your data in column 1 then Text1 remain in column1, Text2 goes to column 2 and subsequently follow. If you wish to use Visual Basic for Application, I mean with code here the example. Try copy and paste below into module and see the result. When you run message prompt text1 follow by text2 and subsequently.

Option Explicit
Sub splittext()
Dim SptTxt As String
Dim Cs As Variant
Dim CsL As Byte
Dim CsU As Byte
Dim i As Byte
SptTxt = "Text1,Text2,Text3,Text4"
Cs = Split(SptTxt, ",")
CsL = LBound(Cs)
CsU = UBound(Cs)
For i = CsL To CsU
    MsgBox (Cs(i))
End Sub

If you have any question or project please e-mail to me, I will reply and create for free.
Click Here to E-mail


Share Button
Share on Facebook
Bookmark this on Digg
Bookmark this on Yahoo Bookmark
Bookmark this on Google Bookmarks
Bookmark this on Delicious

Leave a Comment

If you would like to make a comment, please fill out the form below.

Name (required)

Email (required)



2 Comments so far
  1. Luffy June 30, 2012 4:31 pm

    Thank you!

  2. Gagan Jain September 21, 2012 10:56 am

    thanks a ton!!!!