Merging Rows to Get Complete Names

[new trend]

I have copy pasted some name, desig and email data from a website, after pasting realized that the first middle and last name is in 3 rows. Desig and email ID is in corresponding column to that of name.

What I want? I want to merge complete name in one row without disturbing the corresponding design and email column

Challenge ?

Some names are in 2 rows, some are in 3 rows.

The data is about 15k so require some formula to accomplish what I want.

Can someone help?

[alvin-chung]

Hi, do you mean name always span across A1:A3, desig in B1 and email in C1; then you would like merge A1 thru A3 by concatenating the 3 cells with a space in between?

Does the desig and email already merged? e.g. desig in B1 is actually merged cells of B1:B3 and email in C1 is actually merged cells of C1:C3?

Does your data start from row 1?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[new trend]

Yes. you have got it almost right
1. Data starts from row 2 as row is header
2. The name column sometimes span across A1:A3 and sometimes A1:A2 . If first name, middle name and last name then A1:A3 if First Name and Last Name then A1:A2
3. Design and Email columns are already merged B1:B2 or B1:B3 & C1:C2 or C1:C3 depending on number of rows used for namemerge.JPG

Hope this helps!

[alvin-chung]

Hi, assume column D is where you want to merge the name into one row try:

Please Login or Register  to view this content.

Copy the formula down till the last row of your data.


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[new trend]

Oh wonderful that helped completely. If you can help with the explanation too. Thanks a ton.

[alvin-chung]

Thank you for the feedback, basically what it does is looking at next 2 rows in column B whether it is empty; if it's empty (means merged cell) then add a space in between the names and concatenate them, else just return an empty string


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[new trend]

Thanks for the help!