Inserting a newline in Excel
in [SAS]
|
From: John Hendrickx on 25 Nov 2008 10:59 Does anyone know of a way to insert a newline in Excel using the ExcelXP tagset? I can do it in an RTF file using ^n but that doesn't work in Excel. I can do it manually in Excel by pressing Alt-Enter, which apparently inserts a linefeed character '0A'x. But inserting linefeeds doesn't work. I've tried ^l, <br>, nothing I've tried works. Any suggestions? John Hendrickx Statistician Clinquest Europe BV, Oss - The Netherlands www.clinquest.com
From: Mary on 25 Nov 2008 11:51 I found out (by mistake!) that adding a slash in a define using Proc Report will put a return key in the cell, such as define isv_od_ph_mr/display 'PH/MR OD'; will display the header to the cell as having a PH followed by a newline followed by MR OD. -Mary ----- Original Message ----- From: John Hendrickx To: SAS-L(a)LISTSERV.UGA.EDU Sent: Tuesday, November 25, 2008 9:59 AM Subject: Inserting a newline in Excel Does anyone know of a way to insert a newline in Excel using the ExcelXP tagset? I can do it in an RTF file using ^n but that doesn't work in Excel. I can do it manually in Excel by pressing Alt-Enter, which apparently inserts a linefeed character '0A'x. But inserting linefeeds doesn't work. I've tried ^l, <br>, nothing I've tried works. Any suggestions? John Hendrickx Statistician Clinquest Europe BV, Oss - The Netherlands www.clinquest.com
From: "Terjeson, Mark" on 25 Nov 2008 12:09 Hi Mary, It may or maynot be a slash next time. You probably found out that it relates to the SPLIT= option on your proc statement. e.g. proc report data=mydata SPLIT='/' ... or proc report data=mydata SPLIT='*' ... for define isv_od_ph_mr/display 'PH/MR OD'; or define isv_od_ph_mr/display 'PH*MR OD'; respectively. Hope this is helpful. Mark Terjeson Senior Programmer Analyst Investment Management & Research Russell Investments 253-439-2367 Russell Global Leaders in Multi-Manager Investing -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Mary Sent: Tuesday, November 25, 2008 8:52 AM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: Inserting a newline in Excel I found out (by mistake!) that adding a slash in a define using Proc Report will put a return key in the cell, such as define isv_od_ph_mr/display 'PH/MR OD'; will display the header to the cell as having a PH followed by a newline followed by MR OD. -Mary ----- Original Message ----- From: John Hendrickx To: SAS-L(a)LISTSERV.UGA.EDU Sent: Tuesday, November 25, 2008 9:59 AM Subject: Inserting a newline in Excel Does anyone know of a way to insert a newline in Excel using the ExcelXP tagset? I can do it in an RTF file using ^n but that doesn't work in Excel. I can do it manually in Excel by pressing Alt-Enter, which apparently inserts a linefeed character '0A'x. But inserting linefeeds doesn't work. I've tried ^l, <br>, nothing I've tried works. Any suggestions? John Hendrickx Statistician Clinquest Europe BV, Oss - The Netherlands www.clinquest.com
From: John Hendrickx on 26 Nov 2008 03:49 The split character does work for header rows in ODS destinations but not in the body of the table. For RTF destinations, you can use ^n to insert a newline in the body of the table (where "^" is the ods escapechar). But that doesn't work for ods tagsets.ExcelXP. Neither does including a linefeed character '0A'x in the body of the text. A solution is to use a character that doesn't occur elsewhere in the text, e.g. "\", then replace that with linefeed characters using VBA in Excel. Sub Macro1() Selection.Replace What:="\", Replacement:=Chr(10), LookAt:=xlPart, _ SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Best regards, John Hendrickx On 25 nov, 18:09, Mterje...(a)RUSSELL.COM ("Terjeson, Mark") wrote: > Hi Mary, > > It may or maynot be a slash next time. > > You probably found out that it relates to > the SPLIT= option on your proc statement. > e.g. > proc report data=mydata SPLIT='/' ... > or > proc report data=mydata SPLIT='*' ... > > for > define isv_od_ph_mr/display 'PH/MR OD'; > or > define isv_od_ph_mr/display 'PH*MR OD'; > respectively. > > Hope this is helpful. > > Mark Terjeson > Senior Programmer Analyst > Investment Management & Research > Russell Investments > 253-439-2367 > > Russell > Global Leaders in Multi-Manager Investing > > -----Original Message----- > From: SAS(r) Discussion [mailto:SA...(a)LISTSERV.UGA.EDU] On Behalf Of > > Mary > Sent: Tuesday, November 25, 2008 8:52 AM > To: SA...(a)LISTSERV.UGA.EDU > Subject: Re: Inserting a newline in Excel > > I found out (by mistake!) that adding a slash in a define using Proc > Report > will put a return key in the cell, such as > > define isv_od_ph_mr/display 'PH/MR OD'; > > will display the header to the cell as having a PH followed by a newline > followed by MR OD. > > -Mary > > ----- Original Message ----- > From: John Hendrickx > To: SA...(a)LISTSERV.UGA.EDU > Sent: Tuesday, November 25, 2008 9:59 AM > Subject: Inserting a newline in Excel > > Does anyone know of a way to insert a newline in Excel using the > ExcelXP tagset? I can do it in an RTF file using ^n but that doesn't > work in Excel. I can do it manually in Excel by pressing Alt-Enter, > which apparently inserts a linefeed character '0A'x. But inserting > linefeeds doesn't work. I've tried ^l, <br>, nothing I've tried works. > Any suggestions? > > John Hendrickx > > Statistician > Clinquest Europe BV, Oss - The Netherlandswww.clinquest.com
From: Don Henderson on 26 Nov 2008 11:33
A birdie comments: data test; string='This is a test'; run; ods path work.tmplmst(update) sashelp.tmplmst(read); filename tagset url 'http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl'; proc template; define style styles.XLDefault; parent=styles.Default; style data_unprotect from data / protectspecialchars=off; end; run; quit; ods listing close; ods tagsets.excelxp path='c:\temp' file='text.xml' style=XLDefault options(autofit_height='yes'); proc print data=test; var string / style(data)=data_unprotect; run; quit; ods tagsets.excelxp close; Regards, Donh -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of John Hendrickx Sent: Wednesday, November 26, 2008 3:49 AM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: Inserting a newline in Excel The split character does work for header rows in ODS destinations but not in the body of the table. For RTF destinations, you can use ^n to insert a newline in the body of the table (where "^" is the ods escapechar). But that doesn't work for ods tagsets.ExcelXP. Neither does including a linefeed character '0A'x in the body of the text. A solution is to use a character that doesn't occur elsewhere in the text, e.g. "\", then replace that with linefeed characters using VBA in Excel. Sub Macro1() Selection.Replace What:="\", Replacement:=Chr(10), LookAt:=xlPart, _ SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Best regards, John Hendrickx On 25 nov, 18:09, Mterje...(a)RUSSELL.COM ("Terjeson, Mark") wrote: > Hi Mary, > > It may or maynot be a slash next time. > > You probably found out that it relates to the SPLIT= option on your > proc statement. > e.g. > proc report data=mydata SPLIT='/' ... > or > proc report data=mydata SPLIT='*' ... > > for > define isv_od_ph_mr/display 'PH/MR OD'; or > define isv_od_ph_mr/display 'PH*MR OD'; respectively. > > Hope this is helpful. > > Mark Terjeson > Senior Programmer Analyst > Investment Management & Research > Russell Investments > 253-439-2367 > > Russell > Global Leaders in Multi-Manager Investing > > -----Original Message----- > From: SAS(r) Discussion [mailto:SA...(a)LISTSERV.UGA.EDU] On Behalf Of > > Mary > Sent: Tuesday, November 25, 2008 8:52 AM > To: SA...(a)LISTSERV.UGA.EDU > Subject: Re: Inserting a newline in Excel > > I found out (by mistake!) that adding a slash in a define using Proc > Report will put a return key in the cell, such as > > define isv_od_ph_mr/display 'PH/MR OD'; > > will display the header to the cell as having a PH followed by a > newline followed by MR OD. > > -Mary > > ----- Original Message ----- > From: John Hendrickx > To: SA...(a)LISTSERV.UGA.EDU > Sent: Tuesday, November 25, 2008 9:59 AM > Subject: Inserting a newline in Excel > > Does anyone know of a way to insert a newline in Excel using the > ExcelXP tagset? I can do it in an RTF file using ^n but that doesn't > work in Excel. I can do it manually in Excel by pressing Alt-Enter, > which apparently inserts a linefeed character '0A'x. But inserting > linefeeds doesn't work. I've tried ^l, <br>, nothing I've tried works. > Any suggestions? > > John Hendrickx > > Statistician > Clinquest Europe BV, Oss - The Netherlandswww.clinquest.com |