How to copy data from multiple rows and columns into 1 cell but keep the carriage returns to have separate lines

[Casie]

Hello, I have one spreadsheet with the plant information (name, address, etc) on one line in a sheet. On another sheet I have the contacts at the plant which are listed in several rows (1 row per contact). I need to copy all of the rows and columns from the contacts into 1 big cell on the row that the plant information is on. Concatenate jumbles it. I ideally need it to be pasted just as it looks, on separate lines inside the one cell. Example, plant id (A1) has 8 different rows with different contact information. I need all of the contact information to be in 1 cell on the 1000407 row but retain the visual breakout with the use of the "rows".

1000407​

1000734​

Tennessee Valley Authority

1023286​

Tennessee Valley Authority

1101 Market Street

Chattanooga

TN

Tennessee

37402​

US

1023286​

Tennessee Valley Authority

Watts Bar Nuclear Power Station

Highway 68

Spring City

Tennessee

37381​

Spring City, TN 37381

U.S.A.

P O Box 2000

Spring City

TN

Tennessee

37381​

Spring City, TN 37381

U.S.A.

US*TN*RA

Rhea

TN*03

Southeast

North America

SERC

TVA

TVA

Tennessee Valley Authority

II

423-3651801

423-3651904

1​

Power

4911​

Electric Services [Public Utilities]

O

500​

1-Jan-86​

YEAR

Union

Nuclear

########​

11-Jan-21​

35.60275​

-84.7904​

TVA

Tennessee Valley Authority

www.tva.gov

www.tva.gov

1000734​

TVA

industrialinfo.com - login to access news and information

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Rowe Baggs

Confirmed

Completed (100%)

1000407​	4989896​	Plant Manager	Plant Director	Beth
1000407​	2109929​	Maintenance Manager	Tony
1000407​	3384659​	Purchasing Manager	Purchasing Manager/ System Engineering	Carl
1000407​	1445806​	Engineering Manager	Greg
1000407​	3276201​	Environmental Manager	Dawn
1000407​	1727722​	Safety Manager	Chad
1000407​	3935658​	Utilities Manager	Electrical Supervisor	Randy
1000407​	4367545​	Human Resource Manager	Alicia

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

 

[Larry Haydn]

I do not quite understand what result you are looking to achieve, but here is how it could be done:

Instead of concatenate, you could use the CHAR(10) to break the data to the next row.
If is the similar to vbcrlf in VBA.

For example, if inside cell A1 you want to have the contents of cell B1,B2,B3, the formula in A1 would be:
=B1 & CHAR(10) & B2 & CHAR(10) & B3