[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Forcing a line break in a column in an EXCEL Spreadsheet    -   Excel 2007

Posted on 2012-08-17
8
Medium Priority
?
689 Views
Last Modified: 2012-08-20
I have a column that is a concatenation of multiple columns.  I would like to put these columns into one string.

In doing this is there a way to cause a line feed within a column.  This would be similar to the "<br />" in HTML.
   
If my string reads  "John Doe  Doctor   04/04/1962"

I would like to display
John Doe
Doctor
04/04/1962
0
Comment
Question by:morinia
8 Comments
 
LVL 6

Expert Comment

by:leuqarte
ID: 38306222
add CHAR(10) as the character for a line break (as the equivalent of the BR tag)

http://www.dailydoseofexcel.com/archives/2004/10/19/line-breaks-in-formulas/
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 38306453
You can use Char(10) as leuqarte suggested, e.g.
=CONCATENATE(A1,CHAR(10),B1,CHAR(10),C1)

Open in new window

, but it only works if you set the cell format to wrap text (Alignment...wrap text for pre-2007 Excel, or Wrap text from the Home tab for 2007/2010
0
 

Author Comment

by:morinia
ID: 38306793
Can Char(10) be put into a select statement

(Select fname + '  ' + lname ) as name
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38306968
Hi Morinia,

Yes you can but what's the nature of the Select statement? What is the code trying to accomplish?
0
 

Author Comment

by:morinia
ID: 38306985
I have this select statment

, (Select cast(case_type_desc as nvarchar(25)) + ' - ' +  Cast(row_created as nvarchar(11)) + ' - ' + cast(system_login as nvarchar(20)) + ' - ' + cast(risk_level_desc as nvarchar(22)) + '        Case Closed: ' +  + cast(case_close_date as nvarchar(11)) + ' ' +  cast(close_reason_desc as nvarchar(50)))  as case_lit
From Cases

I would like to put a break after row_created, risk_level_desc, close_reason_desc
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38307011
I'm sorry, this code is unfamiliar to me. Looks like an SQL statement not VBA.
0
 

Author Comment

by:morinia
ID: 38307085
It is SQL.  How would yo do it in VBA.  This is SQL running in EXCEL, so I can put VB code in.
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 38308524
Replace:

Cast(row_created as nvarchar(11)) + ' - '

with:

Cast(row_created as nvarchar(11)) + CHAR(10)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how the fundamental information of how to create a table.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question