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.

 

[Rick Rothstein]

Thanks Rick - Row 23 - and output into Cell N26 - (I already modified for the latter, and also changed your Rows(1) to Rows (23)

Try this...

[table="width: 500"]
[tr]
	[td]Sub SpaceAfterEveryFiveCharacters()
  Range("N26") = RTrim(Format(Application.Trim(Join(Application.Index(Range("N23:IV23").Value, 1, 0), "")), "!" & Application.Rept("@@@@@ ", 40)))
End Sub[/td]
[/tr]
[/table]

By the way, if you are looking for ease of use for your users.... if you add a Form's button to your worksheet and assign the above macro to it, all your users would have to do to produce the required text is click the button.

 

[Zembu]

Try this...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub SpaceAfterEveryFiveCharacters()
  Range("N26") = RTrim(Format(Application.Trim(Join(Application.Index(Range("N23:IV23").Value, 1, 0), "")), "!" & Application.Rept("@@@@@ ", 40)))
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

By the way, if you are looking for ease of use for your users.... if you add a Form's button to your worksheet and assign the above macro to it, all your users would have to do to produce the required text is click the button.

Thanks Rick,

Strange, but whereas with your first version it worked but did it for the entire row, now your new one fails with the following response "Run-time Error "13" Type Mismatch. And when I tried again with your original, it failed in the same way. I suspect this problem is nothing to do with your code, but I don't know what could be causing it. Re the use of the Forms Button - yes I do use that feature and am using it in this case. My general preference though is to have everything done, where practicable, without macros so that the Excel file has a minimum of user actions. But I do like the macro concept - my other problem is being unable to write my own!!

Following on from above, I "re-loaded" it and it now works fine!!! So - Well Done Rick - Many Thanks again for this!!

 

[Zembu]

Thanks Rick,

Strange, but whereas with your first version it worked but did it for the entire row, now your new one fails with the following response "Run-time Error "13" Type Mismatch. And when I tried again with your original, it failed in the same way. I suspect this problem is nothing to do with your code, but I don't know what could be causing it. Re the use of the Forms Button - yes I do use that feature and am using it in this case. My general preference though is to have everything done, where practicable, without macros so that the Excel file has a minimum of user actions. But I do like the macro concept - my other problem is being unable to write my own!!

Following on from above, I "re-loaded" it and it now works fine!!! So - Well Done Rick - Many Thanks again for this!!

____________________________________________

Hello again Rick,

Hoping that you can help further. - This time I think I have no option other than to use a VBA Macro!

I have another file where I tried to modify your VBA "SpaceAfterEveryFiveCharacters" to take input from an array and do the same process. In my file the full array is in cells AZ1 to BX650 (25 cells wide by 650 high) - and this array would always be the same size and always filled. I also tried to make it take the input from a Sheet named "Alpha Output" and write it to a cell on a different sheet - (that's what I would prefer). I failed both times!! When I try it on the same sheet, experimenting with just the first two rows - i.e. (Range("AZ1:BX2"), it works but only for the first row.

 

[Rick Rothstein]

____________________________________________

Hello again Rick,

Hoping that you can help further. - This time I think I have no option other than to use a VBA Macro!

I have another file where I tried to modify your VBA "SpaceAfterEveryFiveCharacters" to take input from an array and do the same process. In my file the full array is in cells AZ1 to BX650 (25 cells wide by 650 high) - and this array would always be the same size and always filled. I also tried to make it take the input from a Sheet named "Alpha Output" and write it to a cell on a different sheet - (that's what I would prefer). I failed both times!! When I try it on the same sheet, experimenting with just the first two rows - i.e. (Range("AZ1:BX2"), it works but only for the first row.

I am not complete clear on what you want for your output...

1) Do you want an output text string for each individual row of data?

2a) If so, did you want those 650 output cells on a single, but different sheet (if so, what is sheet name and what is starting cell)?

2b) Or did you want 650 different sheets, each one to contain the output from one row (if so, how should sheets be named and what cell on each sheet should get the output)?

 

[Zembu]

I am not complete clear on what you want for your output...

1) Do you want an output text string for each individual row of data?

2a) If so, did you want those 650 output cells on a single, but different sheet (if so, what is sheet name and what is starting cell)?

2b) Or did you want 650 different sheets, each one to contain the output from one row (if so, how should sheets be named and what cell on each sheet should get the output)?

Hi Rick,

Thanks for your help - What I would like is a single output text string having all of the letters in the "array", in the same order, left to right, row by row, in the format five letters then a space, repeated to the end. Each cell presently contains one letter. The output should be in one cell.

Also, change of plan, no need for the output cell to be on a different sheet. Let it be on the same sheet as the "array" - source cells AZ1 to BX650 - output cell can be BZ50.

Maybe when I see your VBA for this, and comparing it with your original, it will help me get some insight into how its done

 

[Rick Rothstein]

What I would like is a single output text string having all of the letters in the "array", in the same order, left to right, row by row, in the format five letters then a space, repeated to the end. Each cell presently contains one letter. The output should be in one cell.

You want the output to be in one, single cell? All 18850 characters (including the spaces between the groups of 5)? I do not think you will be able to display all of that on your screen (I don't think the cell can be made tall enough and a cell does not have scroll bars). What about if I pop up a TextBox, with scroll bars, and place the output in it so that you can see all of the rows of data?

 

[Zembu]

You want the output to be in one, single cell? All 18850 characters (including the spaces between the groups of 5)? I do not think you will be able to display all of that on your screen (I don't think the cell can be made tall enough and a cell does not have scroll bars). What about if I pop up a TextBox, with scroll bars, and place the output in it so that you can see all of the rows of data?

The final step is to copy the text from the single cell including spaces out to Notepad or Word, so if I can do that from a text box that will be fine, and it sounds a better way. I know I can copy text out from a single cell, as I have done that already with a cell that contained 32500 characters (but they had no spaces). I know that the maximum cell content is 32767 (last time I checked).

When I click on the cell with 32500 characters it does all display on screen, but it is not very manageable for viewing that way as the image or cell boundary - as you say is not big enough. You can go into it and scroll through.

Thanks for your good thoughts on this. Editing to adding one point - the text box will be no good if it includes carriage returns or such.

 

[Rick Rothstein]

The final step is to copy the text from the single cell including spaces out to Notepad or Word,

We can bypass outputting to a cell or TextBox and write directly to a text file that Notepad or Word can open. I have code to do that prepared, but need you to answer the question I have asked below.

the text box will be no good if it includes carriage returns or such.

You don't want each row of letters from the worksheet to be on its own row in the text file? What do you want... a single "sentence" that is nearly 19,000 characters long? Please clarify.

 

[Zembu]

We can bypass outputting to a cell or TextBox and write directly to a text file that Notepad or Word can open. I have code to do that prepared, but need you to answer the question I have asked below.



You don't want each row of letters from the worksheet to be on its own row in the text file? What do you want... a single "sentence" that is nearly 19,000 characters long? Please clarify.

Hi Rick,

Sorry to be so particular - what you are doing sounds excellent - and yes a single sentence of five letter "words" separated from each other by a space.

 

[Rick Rothstein]

Hi Rick,

Sorry to be so particular - what you are doing sounds excellent - and yes a single sentence of five letter "words" separated from each other by a space.

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"]
[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]
[/table]