Join range of cells into a single cell separated by vertical bar.

[motilulla]

Using Excel 2010

Hello,

I want to join multiple cells C:G columns each row range, into single column “H” separated by vertical bar (having 1 space after and before the vertical bar)

Note: I want a formula or a macro which can be used in Excel 2000 also.

Here is an example sheet with expected results…

Odds.xls
	A	B	C	D	E	F	G	H	I
1
2
3
4
5			n1	n2	n3	n4	n5	Join
6
7			7	13				7 | 13
8			19					19
9			7					7
10			15					15
11
12			3	23				3 | 23
13
14
15			1	15	17	25		1 | 15 | 17 | 25
16			5	7	21			5 | 7 | 21
17			11	17				11 | 17
18
19			1	3	21			1 | 3 | 21
20
21
22			1	9	23	25	27	1 | 9 | 23 | 25 | 27
23			7					7
24			23					23
25
26			23					23
27			5	19				5 | 19
28								5 | 19
29			7					7
30
31
32			5	15				5 | 15
33
Sheet3

Regards,
Moti

 

[Rick Rothstein]

Given the type of values in your cells, this should work...

=SUBSTITUTE(TRIM(C7&" "&D7&" "&E7&" "&F7&" "&G7)," "," | ")

 

[motilulla]

Given the type of values in your cells, this should work...

=SUBSTITUTE(TRIM(C7&" "&D7&" "&E7&" "&F7&" "&G7)," "," | ")

Hello Rick Rothstein, yes your formula worked flawless

I appreciate your kind help.

Good Luck

Kind Regards,
Moti

 

[bebo021999]

Option Explicit
Sub test()
Dim lr&, i&, j&, rng, res(), st As String
lr = Columns("C:H").Find(What:="*", After:=Range("C1"), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
        , SearchFormat:=False).Row
rng = Range("C6:G" & lr).Value
ReDim res(1 To lr, 1 To 1)
For i = 1 To UBound(rng)
    For j = 1 To UBound(rng, 2)
        If rng(i, j) <> "" Then
            st = st & IIf(st = "", "", " | ") & rng(i, j)
        End If
    Next
    res(i, 1) = st: st = ""
Next
Range("H6:H100000").ClearContents
Range("H6").Resize(UBound(rng), 1).Value = res
End Sub

 

[motilulla]

Option Explicit
Sub test()
Dim lr&, i&, j&, rng, res(), st As String
lr = Columns("C:H").Find(What:="*", After:=Range("C1"), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
        , SearchFormat:=False).Row
rng = Range("C6:G" & lr).Value
ReDim res(1 To lr, 1 To 1)
For i = 1 To UBound(rng)
    For j = 1 To UBound(rng, 2)
        If rng(i, j) <> "" Then
            st = st & IIf(st = "", "", " | ") & rng(i, j)
        End If
    Next
    res(i, 1) = st: st = ""
Next
Range("H6:H100000").ClearContents
Range("H6").Resize(UBound(rng), 1).Value = res
End Sub

bebo021999, superb! Thank you for giving a VBA option it worked perfect. This is fast also.

I appreciate your kind help.

Good Luck

Kind Regards,
Moti