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

Casie

New Member
Joined
Jan 11, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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".

1000734​
Tennessee Valley Authority
1023286​
Tennessee Valley Authority1101 Market StreetChattanoogaTNTennessee
37402​
US
1023286​
Tennessee Valley AuthorityWatts Bar Nuclear Power StationHighway 68Spring CityTennessee
37381​
Spring City, TN 37381U.S.A.P O Box 2000Spring CityTNTennessee
37381​
Spring City, TN 37381U.S.A.US*TN*RARheaTN*03SoutheastNorth AmericaSERCTVATVATennessee Valley AuthorityII423-3651801423-3651904
1​
Power
4911​
Electric Services [Public Utilities]O
500​
1-Jan-86​
YEARUnionNuclear
########​
11-Jan-21​
35.60275​
-84.7904​
TVATennessee Valley Authoritywww.tva.govwww.tva.gov
1000734​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
Rowe BaggsConfirmedCompleted (100%)

1000407​
4989896​
Plant ManagerPlant DirectorBeth
1000407​
2109929​
Maintenance ManagerTony
1000407​
3384659​
Purchasing ManagerPurchasing Manager/ System EngineeringCarl
1000407​
1445806​
Engineering ManagerGreg
1000407​
3276201​
Environmental ManagerDawn
1000407​
1727722​
Safety ManagerChad
1000407​
3935658​
Utilities ManagerElectrical SupervisorRandy
1000407​
4367545​
Human Resource ManagerAlicia

 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top