Text String Joiner Macro

[binar]

Fellow Forum Members,
I'm crossing tabulated data over from a PDF file. The problem is that it does not paste into Excel 2007 in the same tabulated format. A paragraph of text pasted into cell A1, most of the time ends up spread across cell A1, A2, and A3. I tried using the merge cell command as a way of joining all the text strings together into a single paragraph in cell A1 and the drawback to this option is that it drops text.

Can anyone out there help me develop a macro that does the following:

I horizontally highlight cells A1, A2, A3 and A4 then click a macro command button and then the text strings inside these cells join together as a single text paragraph inside cell A1. The same thing happens vertically. If I select cells E4, E5, E6 then click a button the text in all three cells join together in cells E4. The order of how the text strings join together is determined by the increasing letter or number of the cell location ID.

In short, is such a Macro possible? I have tried concatenating but this falls short because I need a more automated solution.

Any help will be greatly appreciated. Thanks.

[Bob Phillips]

An example workbook would help binar.

[Kenneth Hobs]

[VBA]Sub VJoin()
Dim v() As Variant, r As Range, c As Range, s As String
Set r = Selection
v() = WorksheetFunction.Transpose(r.Value2)
s = Join(v(), vbCrLf)
Selection.ClearContents
Cells(r.Row, r.Column).Value2 = Replace(Join(v(), vbCrLf), vbTab, vbLf)
Cells(r.Row, r.Column).Select
End Sub[/VBA]

[binar]

Kenneth,
Thanks for your help once again. I am very grateful for you taking the time to post your code. I tried it out and what I learned is that it's half way there. What I mean is that it handles the task of joining text strings in a VERTICAL selection with out a problem. It's amazing !!! No more cut and paste. However, if you try a selection in a horizontal (i.e., C3, D3, E3, F3) direction the script crashes at this line : s = Join(v(),

Would it be too much trouble to modify your code so that it supports both a vertical and horizontal selection? Another option would be to have two separate codes. One for horizontal and one for vertical.

Something else I found interesting is how the code generates a tiny little "?" character as a carriage return. This is no big deal because once I paste it into Notepad it dissappears. Again thanks for your help.

[Bob Phillips]

[vba]

Sub VJoin()
Dim v() As Variant, r As Range, c As Range, s As String
Set r = Selection
If r.Columns.Count > 1 Then

v() = Application.Transpose(Application.Transpose(r.Value2))
Else

v() = Application.Transpose(r.Value2)
End If
s = Join(v(), vbCrLf)
Selection.ClearContents
Cells(r.Row, r.Column).Value2 = Replace(Join(v(), vbCrLf), vbTab, vbLf)
Cells(r.Row, r.Column).Select
End Sub[/vba]

[binar]

Thanks xld,
I tried it out and this code perfectly joins text both in a vertical and horizontal direction. I'm amazed Microsoft does not include something like this as a standard tool in Excel. It makes it much more powerful for working with tabulated data. Thanks for your post and thanks for everyone who posted in this thread.