Hopefully an easy one - turning a list into a row

A

Adam

Hi

I am trying to convert a column of numbers into a row with separating
characters (“•|â€) inserted between each occurrence

So from this

00030707
039027258
060004239
060004882

To this

00030707•|039027258•|060004239•|060004882

The other thing is that this column can have of any amount of numbers listed
in it.


Any ideas?

Many thanks
 
G

Gary''s Student

Function RowThem(r As Range) As String
RowThem = ""
For Each rr In r
If RowThem = "" Then
RowThem = rr.Value
Else
RowThem = RowThem & "•|" & rr.Value
End If
Next
End Function
 
J

Jacob Skaria

Try

Sub Macro1()
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
strData = strData & "•|" & Range("A" & lngRow)
Next
Range("B1") = Mid(strData, 3)
End Sub

If this post helps click Yes
 
R

Rick Rothstein

You didn't say where the column of data is, so I assumed A1 on down. Also,
you didn't say where you wanted the resulting string to be placed at, so I
assumed B1

Sub MakeColumnIntoRow()
Dim LastRow As Long, Destination As Range
Set Destination = Range("B1")
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Destination.Value = Join(WorksheetFunction.Transpose( _
Range("A1:A" & LastRow)), "•|")
End With
End Sub
 
R

Rick Rothstein

I just noticed that I left out some dots (they make the ranges refer back to
the ActiveSheet. Here is the code you should use...

Sub MakeColumnIntoRow()
Dim LastRow As Long, Destination As Range
With ActiveSheet
Set Destination = .Range("B2")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Destination.Value = Join(WorksheetFunction.Transpose( _
.Range("A1:A" & LastRow)), "•|")
End With
End Sub

Also note that this solution does not require any looping.
 
G

Gord Dibben

Just a head's up.

If user has blank cells in the range, your code will produce extra
de-limiters.

Try this revision.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & "*|"
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function


Gord Dibben MS Excel MVP
 
Top