Concatenate columns with cell data containing Carriage Returns

[Rob]

Hi all,

I want to concatenate text in three columns, which would normally be simple.
However, if we take the cells across the three columns, each contains a set
of data using carriage returns (Alt-Enter).

To put some context on the problem, A is First Name, B is Middle Name, C is
Surname.

The data would look like:
A B C
1 David Robert Hughes
Mark Dave Marriott
Martin Benjamin Murray
2 Shaun Andrew Flannagan
Alison Jane Martin

So I would like to combine the data into some sort of output that will lead
to the following, with each :
1 David Robert Hughes
Mark Dave Marriott
Martin Benjamin Murray
2 Shaun Andrew Flannagan
Alison Jane Martin

It is worth noting that some cells may contain 5 entries using carriage
returns, while others may have 2.

Is anyone able to help?

Many thanks,

Rob

 

[Harlan Grove]

Rob wrote...

I want to concatenate text in three columns, which would normally be simple.
However, if we take the cells across the three columns, each contains a set
of data using carriage returns (Alt-Enter).

To put some context on the problem, A is First Name, B is Middle Name, C is
Surname.

The data would look like:
A B C
1 David Robert Hughes
Mark Dave Marriott
Martin Benjamin Murray
2 Shaun Andrew Flannagan
Alison Jane Martin

So I would like to combine the data into some sort of output that will lead
to the following, with each :
1 David Robert Hughes
Mark Dave Marriott
Martin Benjamin Murray
2 Shaun Andrew Flannagan
Alison Jane Martin

It is worth noting that some cells may contain 5 entries using carriage
returns, while others may have 2.

This isn't possible without using temporary cells to hold parsed
individual names from each cell. Which leads to the inescapable
conclusion that what you have is a horrible data structure. Is there
any good reason different individuals' names aren't in different rows?

If you need to keep this data structure, then all you really need are
formulas to parse each cell into separate names in different cells. For
example,

F7:
=LEFT(A1,FIND(CHAR(10),A1&CHAR(10))-1)

F8:
=IF(SUMPRODUCT(LEN(F$7:F7)+1)<LEN(A$1),
MID(A$1,SUMPRODUCT(LEN(F$7:F7)+1)+1,FIND(CHAR(10),A$1&CHAR(10),
SUMPRODUCT(LEN(F$7:F7)+1)+1)-SUMPRODUCT(LEN(F$7:F7)+1)-1),"")

Fill F8 down into F9:F11. Then fill F7:F11 right into G7:H11. Then
concatenate as =F7&G7&H7, etc.

If this isn't acceptable, you'd need to use VBA.

 

[Ashish Mathur]

Hi,

This is an interesting problem to solve. Here is how you can do it.

You will basically have to segregate the data in each cell (entered via Alt
Enter) into various columns. This can be done by using the text to columns
feature (in the the data revenue). Follow the undermentioned procedure

Select delimited in the the text to columns box (Data menu)
In other, enter Alt+010 and finish

This will segregate the data in each cell into various columns.

Regards,


Ashish Mathur

 

[Rob]

I see! I had hoped to avoid the messy creation of new columns.

Data is provided from a database where it outputs the first name data for
all people in one cell using carriage returns, then middle name data for all
people in the next cell on the row, and similarly with the surname.

Thinking about the output once again, I would actually quite like it in a
similarly messy carriage return type format as the data concatenated from
each row will be feeding through to present on individual Powerpoint
presentation slides.

Ashish, where would be the best starting point with VBA?

Many thanks
Rob