split column a data with delimiter

[Mallesh23]

Hi Team,

I have data in column A, and I want to split column a with delimiter _
Data is my Column and I am expecting result as below by using split function in vba. thanks in advance for your help.

Data	output
abc_pqr_xyz	abc	pqr	xyz
pqr_abc_xyz	pqr	abc	xyz
xyz_pqr_xyz	xyz	pqr	xyz
abc_pqr_xyz	abc	pqr	xyz

 

[MARK858]

Why do you need VBA? use Text to Columns. If you do insist on VBA record yourself doing it.

 

[kweaver]

If you don't use Text To Columns, here's one approach:

Book1
	A	B	C	D
1	Data
2	abc_pqr_xyz	abc	pqr	xyz
3	pqr_abc_xyz	pqr	abc	xyz
4	xyz_pqr_xyz	xyz	pqr	xyz
5	abc_pqr_xyz	abc	pqr	xyz
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=LEFT(A2,FIND("_",A2)-1)
C2:C5	C2	=MID(A2,FIND("@",SUBSTITUTE(A2,"_","@",1))+1,(FIND("@",SUBSTITUTE(A2,"_","@",2))-1)-(FIND("@",SUBSTITUTE(A2,"_","@",1))-1)-1)
D2:D5	D2	=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"_","@",2)))

 

[Mallesh23]

Hi Mark and Kweaver,
Thanks for your response, its working, but actually I was trying to split it through vba split function.
and looping through all cells for i = 0 to ubound(arr) like this kind.


Regards
mg

 

[Akuini]

Try:

Sub a1117811a()
Dim c As Range, ary
Application.ScreenUpdating = False
For Each c In Range("A2", Cells(Rows.count, "A").End(xlUp))
    ary = Split(c, "_")
    c.Offset(, 1).Resize(1, UBound(ary) + 1) = ary
Next
Application.ScreenUpdating = True
End Sub

 

[MARK858]

@Mallesh23,I still don't see the advantage in looping into an array to put it in the cells in one go when text to columns does it already but Akuini has posted the code to give you the option you asked for

 

[MARK858]

Just a couple of timings...
2240 records... best time of 5 runs with the 2 codes below... timed by FastExcel V3.
Code a1117811a = 560.38 milliseconds
Code TtoCol = 23.69 milliseconds

Sub a1117811a()
Dim c As Range, ary
Application.ScreenUpdating = False
For Each c In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    ary = Split(c, "_")
    c.Offset(, 1).Resize(1, UBound(ary) + 1) = ary
Next
Application.ScreenUpdating = True
End Sub

Sub TtoCol()
Columns(1).TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
End Sub

 

[Mallesh23]

Hi Mark and Akuini,

Thanks you so much both of you for giving me both the option,
Actually I wanted to learn the method of vba spliting a text.


thanks
mg

 

[Akuini]

Actually I wanted to learn the method of vba spliting a text.

My code uses the split function, so does it answer your question?

 

[Mallesh23]

right its working perfectly.

Thanks
mg