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

Adam,

Is this 'row' all in the same cell?

Mike

"Adam" wrote:

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

--
Gary''s Student - gsnu200904

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
---------------
Jacob Skaria


"Adam" wrote:

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

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

--
Rick (MVP - Excel)


"Adam" wrote in message
...
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

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"Adam" wrote in message
...
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

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

On Wed, 23 Sep 2009 09:10:02 -0700, Gary''s Student
wrote:

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

Thanks all for your help

Both these get me what i'm after

cheers



"Gord Dibben" wrote:

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

On Wed, 23 Sep 2009 09:10:02 -0700, Gary''s Student
wrote:

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