Automatic Merging In Excel

[KetanR]

Hi Folks,

Iam creating an automation spreadsheet (GDoc) but iam now stuck with a small issue. I work with a salesforce tool and have created a report but when i copy directly from the tool but it does not get pasted properly.

I have pasted an example below for reference,

SALESFORCE (REPORT DATA) DATA TO BE COPIED

1	A	B	C	D	E
2	ID NO	NAME	SHIPMENT DETAILS	CLIENT DETAILS	NOTES
3	10	Vikky	00-10	VIKKY-VIKKY MAIK,AC-India	Forms are yet to be filled & Consolidation to be done
4	11	Ankit	00-11	ANKIT-ANKIT MAIK,AB-India	Forms are yet to be filled
5	12	Mike	00-12	MIKE-MIKE CAND,AD-India	Forms are yet to be filled & Consolidation to be done
6	13	Jason	00-13	JASON-JASON BOURNE,SB-America	Forms are yet to be filled
7	14	Austin	00-14	AUSTIN-AUSTIN PAUL,SB-America	Forms are yet to be filled & Consolidation to be done
8	15	Paul	00-15	PAUL-PAUL BLAKE,SB-America	Consolidation to be done
9	16	Jake	00-16	JAKE-JAKE CHRIST,SB-America	Consolidation to be done

<tbody>
</tbody>

"PASTED DATA"

	A	B	C	D	E
1	ID NO	NAME	SHIPMENT DETAILS	CLIENT DETAILS	NOTES
2	10	Vikky	00-10	VIKKY-VIKKY MAIK,AC-India	Forms are yet to be filled.
3				Consolidation to be done
4	11	Ankit	00-11	ANKIT-ANKIT MAIK,AB-India	Forms are yet to be filled
5	12	Mike	00-12	MIKE-MIKE CAND,AD-India	Forms are yet to be filled.
6				Consolidation to be done
7	13	Jason	00-13	JASON-JASON BOURNE,SB-America	Forms are yet to be filled
8	14	Austin	00-14	AUSTIN-AUSTIN PAUL,SB-America	Forms are yet to be filled.
9				Consolidation to be done
10	15	Paul	00-15	PAUL-PAUL BLAKE,SB-America	Consolidation to be done
11	16	Jake	00-16	JAKE-JAKE CHRIST,SB-America	Forms are yet to be filled.
12				Consolidation to be done

<tbody>
</tbody>

When i copy report data and paste just like the above referred "PASTED DATA", 2 notes in a cell divides, separates and gets pasted in two different cells one below the other but in different cells so my doubt is, is there any automated formula which will automatically merge both the details together in one cell just like the reported data immediately after i paste the data. So please try and help me with a formula to bring both the data's together in a same cell.

Thanks in advance.

 

[miless2111s]

I might be a bit of a fudge but the paste seems to be triggering on the "&" character as that is missing from the pasted data. Can you remove that from your original data source? Also why does the last entry (16 - Jake) have additional data "consolidation to be done" when that doesn't appear in the original?

 

[KetanR]

@miless2111s

Hey there, you might be right.

I should not have mentioned "&", Please ignore the last entry as i had created the sample data in a bit of hurry. Sorry for the confusion.

In Sales-force, notes are been updated in a text box one below the other separated it with a dot(.) because its two different actions.

For Ex,

Inside the text box data's are entered like,

- Forms are yet to be filled
- Consolidation to be done

And when i copy after running a periodic report and do the paste it in Gdoc. Data gets pasted just like what you have seen, the pasted data.