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

[Barb @ Work]

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?

 

[Pete_UK]

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

 

[Dave]

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?

 

[Teethless mama]

Right click > Format Cells > Alignment tab > Wrap text > OK out

 

[Dave]

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

 

[Pete_UK]

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

Glad you got it working.

Pete

 

[Barb @ Work]

Thanks Pete_UK for the info. Everything is working great !

 

[Pete_UK]

Glad to hear it - thanks for feeding back.

Pete