VBA: Concatenate data with and without colon from respective rows in B and C cell in all excel sheet

[uglyphoto]

VBA: Concatenate data with and without colon from respective rows in B and C cell in all excel sheet
From D data can be in any cell with and without colon symbol. The empty cells can be anywhere.

Example mock data is below:

Excel 2010

	B	C	D	E	F	G	H	I	J
Get data with colon	Get data without colon
:	1#a;	1	#		a;	:
0:1 (b:a)	14#	1	4		0:1		(b:a)	#
	5a		5		a

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

vba3​

 

[steve the fish]

Heres a basic one using fixed ranges based on your example:

Dim arr, arr2

arr = Range("D2:J5")
ReDim arr2(1 To UBound(arr, 1), 1 To 2)

For i = LBound(arr, 1) To UBound(arr, 1)
    For j = LBound(arr, 2) To UBound(arr, 2)
        If InStr(arr(i, j), ":") > 0 Then
            a = a & arr(i, j)
        Else
            b = b & arr(i, j)
        End If
    Next
    arr2(i, 1) = a: arr2(i, 2) = b
    a = vbNullString: b = vbNullString
Next

Range("B2:C5") = arr2

 

[uglyphoto]

Thank you for your answer.
But I got the wrong result in row 3.
This is mock data actually. So, can I increase the range to anything?

 

[steve the fish]

You can change it to whatever you want. Is the 'wrong' result in the cell that is 0:1? If so excel may be seeing that as a number hence the colon doesnt really exist.

 

[uglyphoto]

Yes, it is 0:1 and its next cell in the same row showing the same problem.
Please help me with the problem.
Thank you again.

 

[steve the fish]

I cant as im not seeing the problem. If i copy what you have and paste it into my workbook and run the macro i get your result.

 

[uglyphoto]

OK.

 

[uglyphoto]

I have added two more symbols i.e, "|" and "~". Can you please modify the range and code.

Excel 2010

	C	D	E	F	G	H	I	J	K	L	M
Get data with |	Get data with ~	Get data with colon	Get data without colon
|	~1	:	1#a;	1	#	~1	a;	:		|
0:1 (b:a)	14#	1	4		0:1		(b:a)	#
x|x|	1~1~		1~1~	x|			x|
	5a		5		a

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[uglyphoto]

Help me to modify the above VBA code as per above table.

 

[uglyphoto]

If @ can be added before all output with above code the result data will not change.