About Us

Gord Dibben

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!

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I insert a colon into a column of existing numbers	jcmonzon	Excel Discussion (Misc queries)	6	April 2nd 23 07:40 PM
Center on colon	WJason	Excel Discussion (Misc queries)	3	August 20th 07 09:54 PM
List cell values seperated by comma if criteria met	Mike Pearson[_2_]	Excel Worksheet Functions	2	June 2nd 07 10:31 PM
Add colon to times	meridklt	New Users to Excel	4	May 11th 07 09:36 PM
change a vertical list of numbers to horizontal list from 1 cell	caz	Excel Discussion (Misc queries)	3	September 27th 06 12:11 PM

Menu

About Us