Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Break cell into multiple lines by line break | Excel Discussion (Misc queries) | |||
How do I enter a line break within a cell? | Excel Discussion (Misc queries) | |||
Manual line break in cell | Excel Discussion (Misc queries) | |||
Remove line break within cell | Excel Discussion (Misc queries) | |||
line break in a cell | Excel Worksheet Functions |