List of numbers seperated by a colon

A

andy

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!
 
P

Peo Sjoblom

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
 
J

JLatham

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.
 
A

andy

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.
 
G

Gord Dibben

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
 
J

Jack Sons

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
 
G

Gord Dibben

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
 
J

Jack Sons

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.
 
G

Gord Dibben

No problem.

Gord

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top