How to line break in one cell via a formula to stack cell entries
 

I have a spreadsheet with 3 columns, each representing a different "code". I
want to write a formula that will evaluate each of the 3 code columns for
each row and, based on the results, assign an English translation to each
code, and stack those translations on top of each other in on cell in a
different column for that row. Each code column will have a max of 3
different code values.

Example:
Column A = Status
Value of "O" = Status is Open
Value of "C" = Status is Closed
Value of "P" = Status is Pending

Column B = Billing Flag
Value of "M" = Monthly Billing
Value of "W" = Weekly Billing
Value of "T" = Transaction Billing

Column C = Customer Type
Value of "C" = Corporate Customer
Value of "P" = Personal Customer
Value of "T" = Tax-Exempt Customer

For row 1, if the value in columns A, B, & C respectively are P, T, P then
column D for row 1, which is where the English translation results are to go,
will be:

Status is Pending
Transaction Billing
Tax-Exempt Customer

Any ideas?

How to line break in one cell via a formula to stack cell entries
 

Set up three simple tables, similar to what you have written. For
example, in X1:Y3:

O Status is Open
C Status is Closed
P Status is Pending

Then in X10:Y12:

M Monthly Billing
W Weekly Billing
T Transaction Billing

and in X20:Y22:

C Corporate Customer
P Personal Customer
T Tax-Exempt Customer

Then in D1 you can put this formula:

=VLOOKUP(A1,X$1:Y$3,2,0)&CHAR(10)&VLOOKUP(B1,X$10: Y
$12,2,0)&CHAR(10)&VLOOKUP(C1,X$20:Y$22,2,0)

Ensure that you format the cell for word-wrap, and make it wide
enough.

Hope this helps.

Pete

On Apr 15, 5:16*pm, Barb @ Work <Barb @
wrote:
I have a spreadsheet with 3 columns, each representing a different "code". I
want to write a formula that will evaluate each of the 3 code columns for
each row and, based on the results, assign an English translation to each
code, and stack those translations on top of each other in on cell in a
different column for that row. *Each code column will have a max of 3
different code values.

Example:
Column A = Status
* * *Value of "O" = Status is Open
* * *Value of "C" = Status is Closed
* * *Value of "P" = Status is Pending

Column B = Billing Flag
* * *Value of "M" = Monthly Billing
* * *Value of "W" = Weekly Billing
* * *Value of "T" = Transaction Billing

Column C = Customer Type
* * *Value of "C" = Corporate Customer
* * *Value of "P" = Personal Customer
* * *Value of "T" = Tax-Exempt Customer

For row 1, if the value in columns A, B, & C respectively are P, T, P then
column D for row 1, which is where the English translation results are to go,
will be:

Status is Pending
Transaction Billing
Tax-Exempt Customer

Any ideas?

How to line break in one cell via a formula to stack cell entr
 

Hi Pete, I am interested in the solution to this one. I tried A1&CHAR(10)&A2,
but it just returned the contents of A1 and A2 separated by a little box, all
on one line. What am I doing wrong?

How to line break in one cell via a formula to stack cell entr
 

Right click Format Cells Alignment tab Wrap text OK out

"Dave" wrote:

Hi Pete, I am interested in the solution to this one. I tried A1&CHAR(10)&A2,
but it just returned the contents of A1 and A2 separated by a little box, all
on one line. What am I doing wrong?

How to line break in one cell via a formula to stack cell entr
 

Hi,
Thanks TM, and sorry Pete. Didn't take notice of all your instructions.
Dave.

How to line break in one cell via a formula to stack cell entr
 

That's quite alright, Dave. I should have put wrap text instead of
word-wrap.

Glad you got it working.

Pete

On Apr 15, 7:24*pm, Dave wrote:
Hi,
Thanks TM, and sorry Pete. Didn't take notice of all your instructions.
Dave.

How to line break in one cell via a formula to stack cell entries
 

Thanks Pete_UK for the info. Everything is working great !

"Barb @ Work" wrote:

I have a spreadsheet with 3 columns, each representing a different "code". I
want to write a formula that will evaluate each of the 3 code columns for
each row and, based on the results, assign an English translation to each
code, and stack those translations on top of each other in on cell in a
different column for that row. Each code column will have a max of 3
different code values.

Example:
Column A = Status
Value of "O" = Status is Open
Value of "C" = Status is Closed
Value of "P" = Status is Pending

Column B = Billing Flag
Value of "M" = Monthly Billing
Value of "W" = Weekly Billing
Value of "T" = Transaction Billing

Column C = Customer Type
Value of "C" = Corporate Customer
Value of "P" = Personal Customer
Value of "T" = Tax-Exempt Customer

For row 1, if the value in columns A, B, & C respectively are P, T, P then
column D for row 1, which is where the English translation results are to go,
will be:

Status is Pending
Transaction Billing
Tax-Exempt Customer

Any ideas?

How to line break in one cell via a formula to stack cell entries
 

Glad to hear it - thanks for feeding back.

Pete

On Apr 15, 8:50*pm, Barb @ Work
wrote:
Thanks Pete_UK for the info. Everything is working great !