Hopefully an easy one - turning a list into a row

  • Thread starter Thread starter Adam
  • Start date Start date
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
 
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
 
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
 
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
 
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.
 
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
 
Back
Top