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.

 

[oldbrewer]

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

C

CH

CHC

CHCU

CHCUC

CHCUCJ

CHCUCJX

CHCUCJXS

CHCUCJXSY

CHCUCJXSYD

CHCUCJXSYDB

CHCUCJXSYDBJ

CHCUCJXSYDBJL

CHCUCJXSYDBJLF

CHCUCJXSYDBJLFL

CHCUCJXSYDBJLFLZ

CHCUCJXSYDBJLFLZV

CHCUCJXSYDBJLFLZVK

CHCUCJXSYDBJLFLZVKI

CHCUCJXSYDBJLFLZVKIY

CHCUCJXSYDBJLFLZVKIYG

CHCUCJXSYDBJLFLZVKIYGH

CHCUCJXSYDBJLFLZVKIYGHF

CHCUCJXSYDBJLFLZVKIYGHFG

CHCUCJXSYDBJLFLZVKIYGHFGK

CHCUCJXSYDBJLFLZVKIYGHFGKK

CHCUCJXSYDBJLFLZVKIYGHFGKKO

CHCUCJXSYDBJLFLZVKIYGHFGKKOT

CHCUCJXSYDBJLFLZVKIYGHFGKKOTK

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKM

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMU

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUD

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDU

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUC

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCX

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXP

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPF

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZ

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZP

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZPG

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZPGE

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZPGER

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZPGER

just check that cell in row 1 is not blank and concatenate it to previous cell in row 2

formula in B2

=IF(B1="","",A2&B1)

<colgroup><col><col span="2"><col><col><col><col span="37"></colgroup><tbody>
</tbody>

 

[oldbrewer]

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

C

CH

CHC

CHCU

CHCUC

CHCUCJ

CHCUCJX

CHCUCJXS

CHCUCJXSY

CHCUCJXSYD

CHCUCJXSYDB

CHCUCJXSYDBJ

CHCUCJXSYDBJL

CHCUCJXSYDBJLF

CHCUCJXSYDBJLFL

CHCUCJXSYDBJLFLZ

CHCUCJXSYDBJLFLZV

CHCUCJXSYDBJLFLZVK

CHCUCJXSYDBJLFLZVKI

CHCUCJXSYDBJLFLZVKIY

CHCUCJXSYDBJLFLZVKIYG

CHCUCJXSYDBJLFLZVKIYGH

CHCUCJXSYDBJLFLZVKIYGHF

CHCUCJXSYDBJLFLZVKIYGHFG

CHCUCJXSYDBJLFLZVKIYGHFGK

CHCUCJXSYDBJLFLZVKIYGHFGKK

CHCUCJXSYDBJLFLZVKIYGHFGKKO

CHCUCJXSYDBJLFLZVKIYGHFGKKOT

CHCUCJXSYDBJLFLZVKIYGHFGKKOTK

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKM

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMU

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUD

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDU

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUC

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCX

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXP

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPF

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZ

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZP

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZPG

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZPGE

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZPGER

CHCUCJXSYDBJLFLZVKIYGHFGKKOTKMUDUCXPFZPGER

just check that cell in row 1 is not blank and concatenate it to previous cell in row 2

CHCUC

more info below scroll down

6

JXSYD

formula giving JXSYD

and to the right

11

BJLFL

=MID($D$10,Z18,5)

formula in B2

16

ZVKIY

21

GHFGK

=IF(B1="","",A2&B1)

26

KOTKM

31

UDUCX

36

PFZPG

41

ER

46

51

56

split the big block into 5 letter pieces

then concatenate them adding a space before each one

JXSYD BJLFL ZVKIY GHFGK KOTKM UDUCX PFZPG ER

<tbody>
</tbody>

 

[Rick Rothstein]

But there could be any number of letters - usually more than 30...

I am not sure what you meant by "the whole width of the Excel display"... can you tell us an absolute maximum number of letters that there can be (do not count the blanks that may appear between letters, I need to know just a count letters only)?

 

[Rick Rothstein]

I am not sure what you meant by "the whole width of the Excel display"... can you tell us an absolute maximum number of letters that there can be (do not count the blanks that may appear between letters, I need to know just a count letters only)?

Ignore the question above, the following macro will output the text you want (with a space after every fifth character) for up to 205 total letters (that number does not count intervening blank cells if any). The macro assumes your letters are in Row 1 and it outputs the text you want to cell A2.

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

 

[Zembu]

Hello OldBrewer,

Many Thanks - simple when you know how - I am obviously out of practice! I would like to make it more automatic or more elegant though. i.e. with your present method we have to go to the last cell and copy that. What I mean by that is that the string of cells that have content is of variable length, so I can't for instance have another "Output" cell that would have in it e.g =N2, as N2 might not be the last cell of wanted output.

As I was writing the above, the answer came to me and I have tried it and it works - using your formula from the right hand end of the row copying to the left, we end up with the full text in the leftmost cell

In my file the formula is =IF(IU49="","",IU49&IV51) at the right end and changes to =IF(N49="","",N49&O51) at the left end (in cell N53).

So then it is simple to have an output cell =N53.

So Excellent - Many Thanks again - now how to add a space after every fifth letter?

 

[Zembu]

Many Thanks Rick,

You will have seen that I responded to OldBrewer - (and I see now that he has posted again - I will get back to him too). However, I have now tried your method and it is obviously excellent as well. I may use Old Brewers as it does not involve the use of a Macro hence making it simpler for all users. But for yours tell me - my row of useful text starts at Column N, so how do I modify your program?

 

[Rick Rothstein]

But for yours tell me - my row of useful text starts at Column N, so how do I modify your program?

Starting with Column N, but on what row?

Also, what cell should get the output?

 

[Zembu]

Starting with Column N, but on what row?

Also, what cell should get the output?

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

 

[Zembu]

Hi Old Brewer,

I have now seen your second part about splitting into 5 and can see that it will work. But Ricks method is easier for that. In my file I have two places where there is that output - in one place I need a space after each 5 and in the other I do not. So I will use your method in the place where I need no spacing and I will use Ricks method in the other. Thanks again!