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