I would like to transpose a vertical range of cells into a single cell so
that each number is seperated by a colon. Here is an example:
A B C
1 50 50:45:30
2 45
3 30

Thanks!

More info is needed, is this a text value or do you intend to use it in time
calculations

=A1&":"&A2&":"&A3


will do this using you example although I suspect it's more than meets the
eye


--
Regards,

Peo Sjoblom



"andy" wrote in message
...
I would like to transpose a vertical range of cells into a single cell so
that each number is seperated by a colon. Here is an example:
A B C
1 50 50:45:30
2 45
3 30

Thanks!

This one is pretty much a manual operation:

In C1 put this formula:
=A1 & ":" & A2 & ":" & A3
alternatively you can use:
=CONCATENATE(A1,":",A2,":",A3)

If this goes on for long, the CONCATENATE may fail you - it has a limited
number of parameters to enter, while the first method is only limited by the
length of your formula.

"andy" wrote:

I would like to transpose a vertical range of cells into a single cell so
that each number is seperated by a colon. Here is an example:
A B C
1 50 50:45:30
2 45
3 30

Thanks!

Thanks Peo. The numbers are actually security numbers for different bonds.
The OLE inbound I am using filters the bonds in the format that each number
is seperated by a colon. For example: 126650BF6:210805BU0:210805CR6. My
list will always be different lengths.

"Peo Sjoblom" wrote:

More info is needed, is this a text value or do you intend to use it in time
calculations

=A1&":"&A2&":"&A3


will do this using you example although I suspect it's more than meets the
eye


--
Regards,

Peo Sjoblom



"andy" wrote in message
...
I would like to transpose a vertical range of cells into a single cell so
that each number is seperated by a colon. Here is an example:
A B C
1 50 50:45:30
2 45
3 30

Thanks!

You can go with the formulas that Peo and Jerry posted or with a UDF which makes
it a bit easier if you are comfortable with VBA.

And before you start note Peo's caveat about turning these into time values if
that's what you foresee.

Function ConCatRange(CellBlock As Range) As String
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

=ConCatRange(A1:A3)

Or a macro which allows non-contiguous cells to be chosen.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub


Gord Dibben MS Excel MVP

On Wed, 22 Aug 2007 12:58:03 -0700, andy wrote:

I would like to transpose a vertical range of cells into a single cell so
that each number is seperated by a colon. Here is an example:
A B C
1 50 50:45:30
2 45
3 30

Thanks!

Gord,

Can the code of ConCat be modified so in the result the last (right most and
therefore superfluous) delimiter will not occur?

Jack Sons
The Netherlands

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
...
You can go with the formulas that Peo and Jerry posted or with a UDF which
makes
it a bit easier if you are comfortable with VBA.

And before you start note Peo's caveat about turning these into time
values if
that's what you foresee.

Function ConCatRange(CellBlock As Range) As String
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

=ConCatRange(A1:A3)

Or a macro which allows non-contiguous cells to be chosen.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or
Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub


Gord Dibben MS Excel MVP

On Wed, 22 Aug 2007 12:58:03 -0700, andy
wrote:

I would like to transpose a vertical range of cells into a single cell so
that each number is seperated by a colon. Here is an example:
A B C
1 50 50:45:30
2 45
3 30

Thanks!

Neither the Function not the macro leaves a superflous delimiter at the end.

If you are getting one I don't know where it comes from.

Have you tested both?


Gord

On Thu, 23 Aug 2007 01:08:47 +0200, "Jack Sons" wrote:

Gord,

Can the code of ConCat be modified so in the result the last (right most and
therefore superfluous) delimiter will not occur?

Jack Sons
The Netherlands

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
.. .
You can go with the formulas that Peo and Jerry posted or with a UDF which
makes
it a bit easier if you are comfortable with VBA.

And before you start note Peo's caveat about turning these into time
values if
that's what you foresee.

Function ConCatRange(CellBlock As Range) As String
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

=ConCatRange(A1:A3)

Or a macro which allows non-contiguous cells to be chosen.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or
Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub


Gord Dibben MS Excel MVP

On Wed, 22 Aug 2007 12:58:03 -0700, andy
wrote:

I would like to transpose a vertical range of cells into a single cell so
that each number is seperated by a colon. Here is an example:
A B C
1 50 50:45:30
2 45
3 30

Thanks!

Gord,

The macro, but although last night it did, now nothing of the kind.
Maybe because it was late last night, 01.08 my time.
I'm really sorry I asked your attention for, in fact, nothing.

Jack.

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
...
Neither the Function not the macro leaves a superflous delimiter at the
end.

If you are getting one I don't know where it comes from.

Have you tested both?


Gord

On Thu, 23 Aug 2007 01:08:47 +0200, "Jack Sons" wrote:

Gord,

Can the code of ConCat be modified so in the result the last (right most
and
therefore superfluous) delimiter will not occur?

Jack Sons
The Netherlands

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
. ..
You can go with the formulas that Peo and Jerry posted or with a UDF
which
makes
it a bit easier if you are comfortable with VBA.

And before you start note Peo's caveat about turning these into time
values if
that's what you foresee.

Function ConCatRange(CellBlock As Range) As String
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

=ConCatRange(A1:A3)

Or a macro which allows non-contiguous cells to be chosen.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or
Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub


Gord Dibben MS Excel MVP

On Wed, 22 Aug 2007 12:58:03 -0700, andy

wrote:

I would like to transpose a vertical range of cells into a single cell
so
that each number is seperated by a colon. Here is an example:
A B C
1 50 50:45:30
2 45
3 30

Thanks!

No problem.

Gord

On Thu, 23 Aug 2007 09:38:08 +0200, "Jack Sons" wrote:

Gord,

The macro, but although last night it did, now nothing of the kind.
Maybe because it was late last night, 01.08 my time.
I'm really sorry I asked your attention for, in fact, nothing.

Jack.

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
.. .
Neither the Function not the macro leaves a superflous delimiter at the
end.

If you are getting one I don't know where it comes from.

Have you tested both?


Gord

On Thu, 23 Aug 2007 01:08:47 +0200, "Jack Sons" wrote:

Gord,

Can the code of ConCat be modified so in the result the last (right most
and
therefore superfluous) delimiter will not occur?

Jack Sons
The Netherlands

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
...
You can go with the formulas that Peo and Jerry posted or with a UDF
which
makes
it a bit easier if you are comfortable with VBA.

And before you start note Peo's caveat about turning these into time
values if
that's what you foresee.

Function ConCatRange(CellBlock As Range) As String
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

=ConCatRange(A1:A3)

Or a macro which allows non-contiguous cells to be chosen.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or
Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub


Gord Dibben MS Excel MVP

On Wed, 22 Aug 2007 12:58:03 -0700, andy

wrote:

I would like to transpose a vertical range of cells into a single cell
so
that each number is seperated by a colon. Here is an example:
A B C
1 50 50:45:30
2 45
3 30

Thanks!