I am using Excel 2000
I have a worksheet which is in effect an index containing a reference number
and an address. the addresses were typed in, one cell per address, using
ALT+ENTER at the end of each line laying it out like an address label. The
cells are formatted a wrapped text.

I am now able to make use of a linked spreadsheet from an Access database
maintained elsewhere and more importantly always up to date. However, each
line of the address is in a different column, 6 in all, and also the managers
name, which is a bonus.

I can use CONCATENATE to bring all the columns into one column but this
produces the managers name and the address as one long line. I tried entering
(CHAR(10)) between the column references but this did not work eg
=(A1&(CHAR(10))&B1).

I did think of using Word but I am not doing a mailmerge and other parts of
my main worksheet have some complex calculations which I do not think Word
could cope with.

QUESTION:
Is there a way, which I can enter as a formula, which would bring all the
fields together, starting each field on a new line.

Thank you in advance for your help
Sean Bishop

=A1&CHAR(10)&B1&CHAR(10)&C1 with wrap text set on should give you what you want.


Gord Dibben MS Excel MVP

On Wed, 15 Nov 2006 14:07:02 -0800, Sean Bishop
wrote:

I am using Excel 2000
I have a worksheet which is in effect an index containing a reference number
and an address. the addresses were typed in, one cell per address, using
ALT+ENTER at the end of each line laying it out like an address label. The
cells are formatted a wrapped text.

I am now able to make use of a linked spreadsheet from an Access database
maintained elsewhere and more importantly always up to date. However, each
line of the address is in a different column, 6 in all, and also the managers
name, which is a bonus.

I can use CONCATENATE to bring all the columns into one column but this
produces the managers name and the address as one long line. I tried entering
(CHAR(10)) between the column references but this did not work eg
=(A1&(CHAR(10))&B1).

I did think of using Word but I am not doing a mailmerge and other parts of
my main worksheet have some complex calculations which I do not think Word
could cope with.

QUESTION:
Is there a way, which I can enter as a formula, which would bring all the
fields together, starting each field on a new line.

Thank you in advance for your help
Sean Bishop

Dear Gord,

Thank you for your answer. It worked.
I think I was overkeen with the brackets.
I had formatted the cells as Text with wrap text and your formula did not
work.
Reformatting as GENERAL with wrap text it worked perfectly.
again, thank you.
Sean Bishop

"Gord Dibben" wrote:

=A1&CHAR(10)&B1&CHAR(10)&C1 with wrap text set on should give you what you want.


Gord Dibben MS Excel MVP

On Wed, 15 Nov 2006 14:07:02 -0800, Sean Bishop
wrote:

I am using Excel 2000
I have a worksheet which is in effect an index containing a reference number
and an address. the addresses were typed in, one cell per address, using
ALT+ENTER at the end of each line laying it out like an address label. The
cells are formatted a wrapped text.

I am now able to make use of a linked spreadsheet from an Access database
maintained elsewhere and more importantly always up to date. However, each
line of the address is in a different column, 6 in all, and also the managers
name, which is a bonus.

I can use CONCATENATE to bring all the columns into one column but this
produces the managers name and the address as one long line. I tried entering
(CHAR(10)) between the column references but this did not work eg
=(A1&(CHAR(10))&B1).

I did think of using Word but I am not doing a mailmerge and other parts of
my main worksheet have some complex calculations which I do not think Word
could cope with.

QUESTION:
Is there a way, which I can enter as a formula, which would bring all the
fields together, starting each field on a new line.

Thank you in advance for your help
Sean Bishop

Thanks for the feedback.

Gord

On Wed, 15 Nov 2006 15:32:01 -0800, Sean Bishop
wrote:

Dear Gord,

Thank you for your answer. It worked.
I think I was overkeen with the brackets.
I had formatted the cells as Text with wrap text and your formula did not
work.
Reformatting as GENERAL with wrap text it worked perfectly.
again, thank you.
Sean Bishop

"Gord Dibben" wrote:

=A1&CHAR(10)&B1&CHAR(10)&C1 with wrap text set on should give you what you want.


Gord Dibben MS Excel MVP

On Wed, 15 Nov 2006 14:07:02 -0800, Sean Bishop
wrote:

I am using Excel 2000
I have a worksheet which is in effect an index containing a reference number
and an address. the addresses were typed in, one cell per address, using
ALT+ENTER at the end of each line laying it out like an address label. The
cells are formatted a wrapped text.

I am now able to make use of a linked spreadsheet from an Access database
maintained elsewhere and more importantly always up to date. However, each
line of the address is in a different column, 6 in all, and also the managers
name, which is a bonus.

I can use CONCATENATE to bring all the columns into one column but this
produces the managers name and the address as one long line. I tried entering
(CHAR(10)) between the column references but this did not work eg
=(A1&(CHAR(10))&B1).

I did think of using Word but I am not doing a mailmerge and other parts of
my main worksheet have some complex calculations which I do not think Word
could cope with.

QUESTION:
Is there a way, which I can enter as a formula, which would bring all the
fields together, starting each field on a new line.

Thank you in advance for your help
Sean Bishop



Gord Dibben MS Excel MVP