Unusual formatting question

[martinbsp]

Hi

bit of an odd question here..

I have a list of URL's that are currently in an Excel file. The URL'
are listed in one column with one on each row, so for example:

link1.ac.uk
link2.ac.uk
link3.ac.uk
link4.ac.uk
link5.ac.uk

What I need to do is merge all of these links into one long line an
add a plus sign between them. In the above example this would mean:

link1.ac.uk+link2.ac.uk+link3.ac.uk+link4.ac.uk+link5.ac.uk


Is there a way of getting Excel (or any other programme) to take th
original list and reformat it as above?

I have several clusters of links that need to be reformatted above.
would do it by hand but there are a few thousand per cluster.

Any help much appreciated.

Marti

 

[Frank Kabel]

Hi Martin
one way:
- download the free add-in Morefunc.xll
(http://longre.free.fr/english/) I´t incorporates the function MCONCAT
- After installing this add-in use the function
=MCONCAT(A1:A20,"+")

 

[JE McGimpsey]

One way:

First note that the maximum number of characters in a cell is 32767 and
only the first 1024 can be displayed without entering manual line feeds,
so your "few thousand (URIs) per cluster" will probably not all fit in
one cell.

Use this UDF:

Public Function MultiCat(ByRef rRng As Excel.Range, _
Optional ByVal sDelimiter As String = "") As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelimiter & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, 2)
End Function

Call as:

=MULTICAT(A1:A100,"+")

If you're not familiar with UDFs, see David McRitchie's "Getting Started
with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm