Joining content of a row of cells into one cell

[Zembu]

Hello All,

I have searched in your site for a solution for my problem, and have tried various solutions for similar questions, modifying some, but without success. I am running Excel 2003.

I have a row of cells, the whole width of the Excel display, each cell containing a formula that results in display of either a letter or nothing. Here for instance is a formula from one of these cells "=IF(V2="","",HLOOKUP(V20,$J$77:$AI$103,V2,TRUE))" The row commences with the letters, then eventually no letters, as the source for these - from elsewhere in the file runs out.

I want to have all the letters from the cells that display them concatenated into one cell. But there could be any number of letters - usually more than 30 - and the number of letters is not always the same.

Here for instance is a row of cells with letters;

C

H

C

U

C

J

X

S

Y

D

B

J

L

F

L

Z

V

K

I

Y

G

H

F

G

K

K

O

T

K

M

U

D

U

C

X

P

F

Z

P

G

E

R

<tbody>
</tbody>

and below is an output that I can get from a Visual Basic Macro that I copied from elsewhere in this site;

C, , , , , , , , , , CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZPGER, , , , , , , CHCUCXVDLCQOETXMICMNWJAHALISUJODYTRKVCHFPQHYOZMBGWYIYKJKMGVUHTPDHATEHQTVAOTZJJIPSMNBIYWJUMLKLAVHEZQNMWFLACGEHCFNEKYKVYDXLZTXUVYIHBPHCCNUZVKHDUMSFPKDTKKHVR, , , , CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZPGER,

<tbody>
</tbody>

There is more but I think that will do as an example.

Here is the Macro that I used and modified slightly - (thanks to hiker95);

Sub Join_UPCS()
' hiker95, 02/23/2016, ME923576
Dim lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("n28").Value = Join(Application.Transpose(Range("n23:n" & lr)), ", ")
Application.ScreenUpdating = True
End Sub

You can see that the above first copies the first letter, then adds commas, then the complete text, then more commas and the rest is from the formulae that exist in the other cells, although on my screen nothing is displayed in these cells.

I tried removing the comma and space at .....Transpose(Range("n23:n" & lr)), ", ") - Thus - ...lr)), "")

This results in a string of letters, still containing an unwanted duplicate of the first, followed by all the other unwanted letters as before.

If anyone can succeed in getting a solution, a further refinement would be to add a space after every 5th letter;

e.g CHCUC JXSYD BJLFL ZVKIY GHFGK KOTKM UDUCX PFZPG ER

Many thanks to any of you who will attempt this.

 

[Zembu]

Rick!!!!!!

Many thanks indeed - it is perfect, especially when using the Notepad font suggestion you made.
You are a Star! - Mr Excel has really excelled!!!

 

[Zembu]

Here you go... the following code will output one long sentence with a single space after every 5 letters to the file name and location of your choice. You can then open that file in Notepad or Word (or any other program capable of opening a text file). My suggestion for Notepad is to change your font to one with fixed-sized letters (such as Courier New) so that the wrapped letters line up under each other in a uniform and easily readable manner.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MakeGroupsOfFiveFile()
  Dim R As Long, FF As Long, Data As Variant, PathFilename As Variant, Result(1 To 650) As String
  Application.Cursor = xlWait
  Data = [AZ1:BX650]
  For R = 1 To 650
    Result(R) = Format(Join(Application.Index(Data, R, 0), ""), "@@@@@ @@@@@ @@@@@ @@@@@ @@@@@")
  Next
  Application.Cursor = xlDefault
  PathFilename = Application.GetSaveAsFilename(FileFilter:="Text File (*.txt), *.txt")
  If PathFilename <> False Then
    FF = FreeFile()
    Open PathFilename For Output As #FF
    Print #FF, Join(Result)
    Close #FF
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Hello Rick,

Thanks again for your previous help.  I would now like to modify the above to give me one long sentence with no spaces.  I tried to modify it myself, but clearly I do not understand how to do it!

Best wishes, Andrew

 

[Rick Rothstein]

Here you go... the following code will output one long sentence with a single space after every 5 letters to the file name and location of your choice. You can then open that file in Notepad or Word (or any other program capable of opening a text file). My suggestion for Notepad is to change your font to one with fixed-sized letters (such as Courier New) so that the wrapped letters line up under each other in a uniform and easily readable manner.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MakeGroupsOfFiveFile()
  Dim R As Long, FF As Long, Data As Variant, PathFilename As Variant, Result(1 To 650) As String
  Application.Cursor = xlWait
  Data = [AZ1:BX650]
  For R = 1 To 650
    [B][COLOR="#FF0000"]Result(R) = Format(Join(Application.Index(Data, R, 0), ""), "@@@@@ @@@@@ @@@@@ @@@@@ @@@@@")[/COLOR][/B]
  Next
  Application.Cursor = xlDefault
  PathFilename = Application.GetSaveAsFilename(FileFilter:="Text File (*.txt), *.txt")
  If PathFilename <> False Then
    FF = FreeFile()
    Open PathFilename For Output As #FF
    Print #FF, Join(Result)
    Close #FF
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Hello Rick,

Thanks again for your previous help. I would now like to modify the above to give me one long sentence with no spaces.

Change the red highlighted line of code to this...

Result(R) = Join(Application.Index(Data, R, 0), "")

 

[Zembu]

Thanks Rick, However, it still inserts a space after every 25 characters (as each line is of 25 cells) Can that space be removed please?

Regards, Andrew

 

[Rick Rothstein]

Thanks Rick, However, it still inserts a space after every 25 characters (as each line is of 25 cells) Can that space be removed please?

Sorry, one more change is needed. Keep the change I gave you earlier (it is implemented in the code below) and change the red line of code below to this...

Print #FF, Join(Result, "")

[table="width: 500"]
[tr]
	[td]Sub MakeGroupsOfFiveFile()
  Dim R As Long, FF As Long, Data As Variant, PathFilename As Variant, Result(1 To 650) As String
  Application.Cursor = xlWait
  Data = [AZ1:BX650]
  For R = 1 To 650
    Result(R) = Join(Application.Index(Data, R, 0), "")  Next
  Application.Cursor = xlDefault
  PathFilename = Application.GetSaveAsFilename(FileFilter:="Text File (*.txt), *.txt")
  If PathFilename <> False Then
    FF = FreeFile()
    Open PathFilename For Output As #FF
    [B][COLOR="#FF0000"]Print #FF, Join(Result)[/COLOR][/B]
    Close #FF
  End If
End Sub[/td]
[/tr]
[/table]

 

[Zembu]

Many Thanks Rick - excellent!!!!!!

Best wishes, Andrew

 

[Zembu]

Hello Rick,

A question arises, to which there may be no answer! I noticed that the file produced by your macro code has at its end a Line Feed and a Carriage return.

Is there any possible modification to your code to prevent these appearing? This site https://danielmiessler.com/study/crlf/ has some info about these LF and CR characters, from which I suspect that nothing can be done?

Best wishes

Andrew

 

[Rick Rothstein]

Hello Rick,

A question arises, to which there may be no answer! I noticed that the file produced by your macro code has at its end a Line Feed and a Carriage return.

Is there any possible modification to your code to prevent these appearing? This site https://danielmiessler.com/study/crlf/ has some info about these LF and CR characters, from which I suspect that nothing can be done?

I think if you add a semi-colon at the end of the line I highlighted in red in Message #25, then that will suppress the Cr/Lf at the end...

Print #FF, Join(Result);

 

[Zembu]

Rick!! - what can I say - AMAZING - as you said - the addition of the semi-colon has resulted in no CR LF.

Many thanks again, Andrew