Using CONCATENATE

venuswilliams

New Member
Joined
Sep 20, 2006
Messages
4
I have several worksheets that i want to combine into one with the data. I am using CONCATENATE to combine them, but they are all merging together as one paragraph instead of line by line from the other sheets.

Can you help me?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows
venuswilliams

Welcome to the Mr Excel board!

You would need to give some more detail about what you have and where and what results you expect and where. Give some sample data and sample results.
 

venuswilliams

New Member
Joined
Sep 20, 2006
Messages
4
I have several worksheets with the same information. I want to combine them on one worksheet. On each sheet, the column may say in worksheet1 (clean towels), the worksheet, same column will say (dirty tiles), and the third worksheet may say (soap scum in sink). I want to join those columns together into one worksheet so that all 3 of those items will be in the same column, but they are joining together as a paragraph, i want for instance the first one that says (clean towels) to be on a line by itself, then under it will be the next one (dirty tiles), etc., instead i am using CONCATENATE and they are running into each other with a space inbetween (ex. clean towels dirty tiles soap scum).
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows
venuswilliams

Thanks, that gives a bit better idea. Could you also paste in the CONCATENATE formula that you currently have (even though it doesn't give the result you want)?

Can you also confirm that in your example, you would want:
'clean towels' in cell A1
'dirty tiles' in cell A2
'soap scum in sink' in cell A3?
 

venuswilliams

New Member
Joined
Sep 20, 2006
Messages
4

ADVERTISEMENT

That is wrong (dirty towels-worksheet1-cell A1) (dirtyt tiles-worksheet2-cell A1) (soap scum in sink-worksheet3-cell A1). They are the same cells in different worksheets that i want jointed together in another worksheet showing all of them together. But mine are joined as a paragraph instead of one under the other. Here is the formula (=CONCATENATE('1N'!F4,'2S'!F4,'4th fl'!F4)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows
OK, I still not absolutely sure what you mean by 'showing them together' but try this.
1. Formula in cell F4 of my 'Concatenate' sheet below is:
='1N'!F4&CHAR(10)&'2S'!F4&CHAR(10)&'4th fl'!F4
2. Select cell F4 then Format|Cells...|Alignment|Wrap Text|OK
3. With the cell still selected: Format|Row|AutoFit
Mr Excel.xls
ABCDEFG
1
2
3
4clean towels dirty tiles soap scum in sink
5
Concatenate
 

venuswilliams

New Member
Joined
Sep 20, 2006
Messages
4
Peter,

You are the greatest! I was trying to figure this out for a couple of weeks. Now thanks to you i can get my project under way. I really, really appreciate it.

Thank you,
Venus
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows
Peter,

You are the greatest! I was trying to figure this out for a couple of weeks. Now thanks to you i can get my project under way. I really, really appreciate it.

Thank you,
Venus
No problem, glad to help.

Looking back on it you might see that it was a little difficult to determine exactly what you wanted because in Excel when people refer to 'lines' they often really mean 'rows' or 'cells'. You didn't actually say that you wanted the results on different lines IN THE SAME CELL.

Anyway, glad you got what you wanted and trust that if you have any more difficulties, the Mr Excel board will help you resolve them. :biggrin:
 

Forum statistics

Threads
1,141,720
Messages
5,708,090
Members
421,546
Latest member
delatollas

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
Top