I have data with symbols like "|" and "~" and ":" from H to say N column. With VBA (using switch statement) get, In Column B and C: Concatenate data with and without "|" from respective rows In Column D and E: Concatenate data with and without "~" from respective rows In Column F and G: Concatenate data with and without ":" from respective rows Excel 2010 #E0E0F0[/URL] "] A B C D E F G H I J K L M N 1 2 1 Get data with | Get data without | Get data with ~ Get data without ~ Get data with colon Get data without colon #FFFF00[/URL] , align: center"]data1 #FFFF00[/URL] , align: center"]data2 #FFFF00[/URL] , align: center"]data3 #FFFF00[/URL] , align: center"]data4 #FFFF00[/URL] , align: center"]data5 #FFFF00[/URL] , align: center"]data6 #FFFF00[/URL] , align: center"]data7 3 2 #FFFF00[/URL] "] | #FFFF00[/URL] "] 1 # ~1 a; : #FFFF00[/URL] "] ~1 #FFFF00[/URL] "] 1 # a; : | #FFFF00[/URL] "] : #FFFF00[/URL] "] 1 # ~1 a; | #FFC000[/URL] , align: right"]1 #FFC000[/URL] "]# #FFC000[/URL] "]~1 #FFC000[/URL] "]a; #FFC000[/URL] "]: #FFC000[/URL] , align: right"] #FFC000[/URL] "]| 4 3 #FFFF00[/URL] , align: right"] #FFFF00[/URL] "] 1 4 0:01 (b:a) # #FFFF00[/URL] , align: right"] #FFFF00[/URL] "] 1 4 0:01 (b:a) # #FFFF00[/URL] "] 0:1 (b:a) #FFFF00[/URL] "] 1 4 # #FFC000[/URL] , align: right"]1 #FFC000[/URL] , align: right"]4 #FFC000[/URL] , align: right"] #FFC000[/URL] , align: right"]0:01 #FFC000[/URL] , align: right"] #FFC000[/URL] "](b:a) #FFC000[/URL] "]# 5 4 #FFFF00[/URL] "] x| x| #FFFF00[/URL] , align: right"] #FFFF00[/URL] , align: right"] #FFFF00[/URL] "] x| x| #FFFF00[/URL] , align: right"] #FFFF00[/URL] "] x| x| #FFC000[/URL] "]x| #FFC000[/URL] , align: right"] #FFC000[/URL] , align: right"] #FFC000[/URL] , align: right"] #FFC000[/URL] "]x| #FFC000[/URL] , align: right"] #FFC000[/URL] , align: right"] 6 5 #FFFF00[/URL] , align: right"] #FFFF00[/URL] "] 5 a #FFFF00[/URL] , align: right"] #FFFF00[/URL] "] 5 a #FFFF00[/URL] , align: right"] #FFFF00[/URL] "] 5 a #FFC000[/URL] , align: right"] #FFC000[/URL] , align: right"]5 #FFC000[/URL] , align: right"] #FFC000[/URL] "]a #FFC000[/URL] , align: right"] #FFC000[/URL] , align: right"] #FFC000[/URL] , align: right"] Sheetprofit

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

VBA: Concatenate data with and without symbol from respective rows

Thread starter uglyphoto
Start date Jan 11, 2019
Tags

concatenate data respective rows vba & excel 2010

uglyphoto

New Member

Jan 11, 2019

I have data with symbols like "|" and "~" and ":" from H to say N column.
With VBA (using switch statement) get,
In Column B and C: Concatenate data with and without "|" from respective rows
In Column D and E: Concatenate data with and without "~" from respective rows
In Column F and G: Concatenate data with and without ":" from respective rows

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	1	Get data with \|	Get data without \|	Get data with ~	Get data without ~	Get data with colon	Get data without colon	data1	data2	data3	data4	data5	data6	data7
3	2	\|	1 # ~1 a; :	~1	1 # a; : \|	:	1 # ~1 a; \|	1	#	~1	a;	:		\|
4	3		1 4 0:01 (b:a) #		1 4 0:01 (b:a) #	0:1 (b:a)	1 4 #	1	4		0:01		(b:a)	#
5	4	x\| x\|			x\| x\|		x\| x\|	x\|				x\|
6	5		5 a		5 a		5 a		5		a