Help me code VBA split string!!!

[Nguyen Anh Dung]

i have column A,B, C as below

I want split column B,C with character "|" result as picture below

Thanks and Best regards!!!

 

[HongRu]

Each cell in column B and C has a character "|", and only one "|" ?

 

[kvsrinivasamurthy]

Input data

Book1.xlsm
	A	B	C
1	100	aaa | ddd1	26565|9877
2	101	aaa | ddd2	26565|9878
3	102	aaa | ddd3	26565|9879
4
Sheet1

Output data

Book1.xlsm
	A	B	C
1	100	aaa	26565
2	100	ddd1	9877
3	101	aaa	26565
4	101	ddd2	9878
5	102	aaa	26565
6	102	ddd3	9879
7
Sheet2

VBA code.

Sub Splitdata()
Dim A, X, Y
Dim Dic As Object
Dim Lr&, T&, Ta&, Z&
Set Dic = CreateObject("scripting.dictionary")
With Sheets("Sheet1")
Lr = .Range("A" & Rows.Count).End(xlUp).Row
A = .Range("A1:C" & Lr)
End With
For T = 1 To UBound(A, 1)
X = Split(A(T, 2), "|")
Y = Split(A(T, 3), "|")
    For Ta = 0 To UBound(X, 1)
    Z = Z + 1
    Dic.Item(Z) = Array(A(T, 1), Trim(X(Ta)), Trim(Y(Ta)))
    Next Ta
X = "": Y = ""
Next T

With Sheets("Sheet2").Range("A1")
.CurrentRegion.Clear
.Resize(Z, 3) = Application.Index(Dic.items, 0, 0)
End With

End Sub

 

[HongRu]

Sorry to del message.

 

[HongRu]

Try.

Sub mySplit()
    Dim Rng As Range
    Set Rng = Range("A1") 'A1 is the first cell of data
    
    Dim Output As Range
    Set Output = Range("E1") 'E1 is the first cell of output cells
    
    For i = 0 To Rng.CurrentRegion.Rows.Count - 1
        For j = 0 To 1
            Output.Offset(i * 2 + j, 0) = Rng.Offset(i, 0)
            Output.Offset(i * 2 + j, 1) = Split(Rng.Offset(i, 1), "|")(j)
            Output.Offset(i * 2 + j, 2) = Split(Rng.Offset(i, 2), "|")(j)
        Next j
    Next i
End Sub

 

[Nguyen Anh Dung]

Input data

Book1.xlsm
	A	B	C
1	100	aaa | ddd1	26565|9877
2	101	aaa | ddd2	26565|9878
3	102	aaa | ddd3	26565|9879
4
Sheet1

Output data

Book1.xlsm
	A	B	C
1	100	aaa	26565
2	100	ddd1	9877
3	101	aaa	26565
4	101	ddd2	9878
5	102	aaa	26565
6	102	ddd3	9879
7
Sheet2

VBA code.

Sub Splitdata()
Dim A, X, Y
Dim Dic As Object
Dim Lr&, T&, Ta&, Z&
Set Dic = CreateObject("scripting.dictionary")
With Sheets("Sheet1")
Lr = .Range("A" & Rows.Count).End(xlUp).Row
A = .Range("A1:C" & Lr)
End With
For T = 1 To UBound(A, 1)
X = Split(A(T, 2), "|")
Y = Split(A(T, 3), "|")
    For Ta = 0 To UBound(X, 1)
    Z = Z + 1
    Dic.Item(Z) = Array(A(T, 1), Trim(X(Ta)), Trim(Y(Ta)))
    Next Ta
X = "": Y = ""
Next T

With Sheets("Sheet2").Range("A1")
.CurrentRegion.Clear
.Resize(Z, 3) = Application.Index(Dic.items, 0, 0)
End With

End Sub

Thanks you so much!!!

 

[Nguyen Anh Dung]

Try.

Sub mySplit()
    Dim Rng As Range
    Set Rng = Range("A1") 'A1 is the first cell of data
   
    Dim Output As Range
    Set Output = Range("E1") 'E1 is the first cell of output cells
   
    For i = 0 To Rng.CurrentRegion.Rows.Count - 1
        For j = 0 To 1
            Output.Offset(i * 2 + j, 0) = Rng.Offset(i, 0)
            Output.Offset(i * 2 + j, 1) = Split(Rng.Offset(i, 1), "|")(j)
            Output.Offset(i * 2 + j, 2) = Split(Rng.Offset(i, 2), "|")(j)
        Next j
    Next i
End Sub

Thanks you so much!!!

 

[Nguyen Anh Dung]

Thanks you so much!!!

i have run but error

 

[HongRu]

Each cell in column B and C has a character "|", and only one "|" ?
If not always do have, try this one.

Sub mySplit()
    Dim Rng As Range
    Set Rng = Range("A1") 'A1 is the first cell of data
    
    Dim Output As Range
    Set Output = Range("E1") 'E1 is the first cell of output cells
    
    For i = 0 To Rng.CurrentRegion.Rows.Count - 1
        mySplit_1 = Split(Rng.Offset(i, 1), "|")
        mySplit_2 = Split(Rng.Offset(i, 2), "|")
        If UBound(mySplit_1) <= UBound(mySplit_2) Then
            myUbound = UBound(mySplit_1)
        Else
            myUbound = UBound(mySplit_2)
        End If
        For j = 0 To myUbound
            Output.Offset(k, 0) = Rng.Offset(i, 0)
            Output.Offset(k, 1) = mySplit_1(j)
            Output.Offset(k, 2) = mySplit_2(j)
            k = k + 1
        Next j
    Next i
End Sub

i have run but error
View attachment 123609

 

[Nguyen Anh Dung]

Each cell in column B and C has a character "|", and only one "|" ?
If not always do have, try this one.

Sub mySplit()
    Dim Rng As Range
    Set Rng = Range("A1") 'A1 is the first cell of data
   
    Dim Output As Range
    Set Output = Range("E1") 'E1 is the first cell of output cells
   
    For i = 0 To Rng.CurrentRegion.Rows.Count - 1
        mySplit_1 = Split(Rng.Offset(i, 1), "|")
        mySplit_2 = Split(Rng.Offset(i, 2), "|")
        If UBound(mySplit_1) <= UBound(mySplit_2) Then
            myUbound = UBound(mySplit_1)
        Else
            myUbound = UBound(mySplit_2)
        End If
        For j = 0 To myUbound
            Output.Offset(k, 0) = Rng.Offset(i, 0)
            Output.Offset(k, 1) = mySplit_1(j)
            Output.Offset(k, 2) = mySplit_2(j)
            k = k + 1
        Next j
    Next i
End Sub

Thanks you so much!!!
Best regards,
Nguyen Anh Dung