Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Break cell into multiple lines by line break Chia Excel Discussion (Misc queries) 1 August 20th 06 06:37 AM
How do I enter a line break within a cell? Striving for efficiency Excel Discussion (Misc queries) 4 November 17th 05 09:10 PM
Manual line break in cell GKW in GA Excel Discussion (Misc queries) 1 November 6th 05 06:28 AM
Remove line break within cell Tonya Excel Discussion (Misc queries) 3 July 6th 05 08:41 PM
line break in a cell luvgreen Excel Worksheet Functions 2 June 14th 05 06:48 PM


All times are GMT +1. The time now is 09:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"