Taking 6 Text Cells and combining into 1 consolidated cell

[sdaddy]

Hello All,
I have a Consolidated list that pulls various values from Tabs 1-111

The column i am currently working on needs to pull the following:

Cells: E139,E140,E141,E142,E143,E144,E145 ((E139:E145) which all contain text))

In the column i am working on i need each individuals cell "value/text" to be seperated with a comma or semi-colon and consolidated into one cell.

e.g. if the values were:
E139 = One
E140 = Two
E141 = Three
etc

the cell i need, would display: One; Two; Three

I am currently using ='01'!E139+'01'!E140+'01'!E141 etc (e.g. referring to File Tab 1 and cells E139:E145, but it doesnt work as they are text values rather than numbers i suppose.

This worksheet also needs to be sorted a number of times. Therefore i believe indirect wouldnt work as sorting with indirect has given me a number of problems in the past.

Any suggestions, much appreciated.

[mewingkitty]

You can use the & symbol
ie

=a1&","&a2&","

Or you can post a sample workbook and I can make something that'll do it permanently with vba. Whatever works for ya.

[bstubbs]

Try using "&" rather than "+".

HTH.

Bob

[sdaddy]

Thanks bstubbs,
the & worked a treat, however, how would i add a "space", "comma" or semi-colon" to define the end of each cell and the start of a new one

Therefore, at the moment using:

='01'!$E$139& '01'!$E$140& '01'!$E$141& '01'!$E$142& '01'!$E$143& '01'!$E$144& '01'!$E$145

But need a semi colon and space to seperate each cell..

Thanks again all

[Trebor76]

Hi sdaddy,

Try this:

='01'!$E$139& "; "&'01'!$E$140& "; "&'01'!$E$141& "; "&'01'!$E$142& "; "&'01'!$E$143& "; "&'01'!$E$144& "; "&'01'!$E$145

HTH

Robert