Join the text in cells with separators

[nando88]

I wish to make a macro in vba, to join the text in a selected range, substitute all the spaces with %20, and add |, after every cell. I wish to have this result in the cell next to the last cell with data in the row I am working in, since I am only going to be selecting cells in a row.
I have typed some code, but it doesn't seem to work, can someone please help me?

Function wMerge(Rng As Range)
Dim col1 As Integer
Dim col2 As Integer
Dim total As Integer
Dim subs As String
Dim concat1 As String
Dim concat2 As String
Dim s As String

part1 = Left(Rng.Address, Application.WorksheetFunction.Find("-", Rng.Address) - 1)
part2 = Right(Rng.Address, Len(Rng.Address) - Application.WorksheetFunction.Find("-", Rng.Address))
col1 = Range(Left(part1, 1)).Column
col2 = Range(part2).Column
s = Range(part2).Address(False, False)
    ColumnName = Left(s, Len(s) - Len(Format(Rng.Row, "0")))
For i = 1 To total
    concat1 = Replace(ActiveSheet.Cells(Range(part1).Row, total).Value, " ", "%20")
    concat2 = concat1 & "|"
Next i
ActiveSheet.Cells(total, Range(part2).Row).Value (concat2)
End Function

 

[Snakehips]

nando88,

You have coded for a function which I will assume you will use in the appropriate last cell?

If so then is this what you are wanting....
11

*	A	B	C	D
1	Stuff	More Stuff	Enough Stuff	Stuff|More%20Stuff|Enough%20Stuff|

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:115px;"><col style="width:115px;"><col style="width:115px;"><col style="width:451px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D1 =TestMerge(A1:C1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Function TestMerge(Rng As Range)


For Each cell In Rng
Strg = Strg & WorksheetFunction.Substitute(cell.Value, " ", "%20") & "|"


Next cell


TestMerge = Strg
End Function

Hope that helps.