Manipulating Text in Rows with COUNTA and CONCATENATE

[NigelVII]

I have data in rows like this:

Sally Bob Dave Wally
Jim Dave Henry Frank Ralph John Ringo George Paul
Henry Bob

There can be as many as 25 names.

I am trying to find a way to CONCATENATE the list of names with a CHAR(10)
between each name in order to have a list of names in a single cell that are
each on its own line, with out any trailing blank lines in the cell.

I have been trying to combine a COUNTA and a CONCATENATE without much luck.

Can anyone provide some help with this or a different approach?

 

[Wigi]

Hi

Here's a VBA approach:


Option Explicit
Option Base 1

Sub stripafterwords()

Dim r As Range
Dim i As Integer
Dim arrNames() As String

For Each r In Range("A1", Range("A" & Rows.Count).End(xlUp))

For i = 1 To Cells(r.Row, Columns.Count).End(xlToLeft).Column

ReDim Preserve arrNames(i)

arrNames(i) = Cells(r.Row, i).Value

Next

Cells(r.Row, 26).Value = Join(arrNames, Chr(10))

Next

End Sub

 

[NigelVII]

Thank you!

This works perfectly and is good compact code.

I actually got the COUNTA/CONCATENATE to work (counted the number of names,
then depending on that value went through an IF/ELSIF to concatenate).
Very,very cumbersome!!

I was hoping was there was a streamlined way to do an array for these. My
experience in Excel arrays is nil. But looking at your code - doesn't seem
too scary!

Thank you again - most grateful!

Kindest Regards,
NigelVII