<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Verdana
}
</style>
</head>
<body class='hmmessage'>
<meta http-equiv="Content-Type" content="text/html; charset=unicode">
<meta name="Generator" content="Microsoft SafeHTML">
<style>
.hmmessage P
{margin:0px;padding:0px;}
body.hmmessage
{font-size:10pt;font-family:Verdana;}
</style>
<br>Hi ALL:<br><br><br> I like Jay's solution. And I would add a matchpattern to make sure that the NCONVERT was getting the correct digits.<br><br> i.e. Define good_num = 1 if matchpattern ( zerofill (amount_string ), &<br> "(###!,###!,###!.##)|(0000###!,###!.##)|(00000000###!.##)"<br><br> Regards,<br> Peter<br> <br> <br><br><br><br><br><hr id="stopSpelling">From: JNalder@ascprofiles.com<br>To: Chuck.J.Lowe@questdiagnostics.com; powerh-l@lists.sowder.com<br>Date: Mon, 31 Aug 2009 10:24:28 -0700<br>Subject: RE: Formatting a number in PowerHouse - version 8.49.E<br><br>
<style>
.ExternalClass p.EC_MsoNormal, .ExternalClass li.EC_MsoNormal, .ExternalClass div.EC_MsoNormal
{margin-bottom:.0001pt;font-size:12.0pt;font-family:'Times New Roman','serif';}
.ExternalClass a:link, .ExternalClass span.EC_MsoHyperlink
{color:blue;text-decoration:underline;}
.ExternalClass a:visited, .ExternalClass span.EC_MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}
.ExternalClass p
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:'Times New Roman','serif';}
.ExternalClass span.EC_EmailStyle19
{font-family:'Calibri','sans-serif';color:#1F497D;}
.ExternalClass .EC_MsoChpDefault
{font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;}
.ExternalClass div.EC_Section1
{page:Section1;}
</style>
<div class="EC_Section1">
<p class="EC_MsoNormal"><span style="font-size: 11pt; font-family: 'Calibri','sans-serif'; color: rgb(31, 73, 125);">Try right justifying it into a fixed length field and use subscripting</span></p>
<p class="EC_MsoNormal"><span style="font-size: 11pt; font-family: 'Calibri','sans-serif'; color: rgb(31, 73, 125);"> </span></p>
<p class="EC_MsoNormal"><span style="font-size: 11pt; font-family: 'Calibri','sans-serif'; color: rgb(31, 73, 125);">Define formatted-string char*14 = rj(pipe-delimited-field[1:14]) ; 999,999,999.00</span></p>
<p class="EC_MsoNormal"><span style="font-size: 11pt; font-family: 'Calibri','sans-serif'; color: rgb(31, 73, 125);">Define amount-string char*14 = formatted-string[1:3] + formatted-string[5:3] + formatted-string[9:6]</span></p>
<p class="EC_MsoNormal"><span style="font-size: 11pt; font-family: 'Calibri','sans-serif'; color: rgb(31, 73, 125);">Define amt float = nconvert( amount-string)</span></p>
<p class="EC_MsoNormal"><span style="font-size: 11pt; font-family: 'Calibri','sans-serif'; color: rgb(31, 73, 125);"> </span></p>
<p class="EC_MsoNormal"><span style="font-size: 11pt; font-family: 'Calibri','sans-serif'; color: rgb(31, 73, 125);">Jay Nalder.</span></p>
<p class="EC_MsoNormal"><span style="font-size: 11pt; font-family: 'Calibri','sans-serif'; color: rgb(31, 73, 125);"> </span></p>
<div>
<div style="border-style: solid none none; border-color: rgb(181, 196, 223) -moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: 3pt 0in 0in;">
<p class="EC_MsoNormal"><b><span style="font-size: 10pt; font-family: 'Tahoma','sans-serif';">From:</span></b><span style="font-size: 10pt; font-family: 'Tahoma','sans-serif';"> powerh-l-bounces+jnalder=ascprofiles.com@lists.sowder.com [mailto:powerh-l-bounces+jnalder=ascprofiles.com@lists.sowder.com]
<b>On Behalf Of </b>Lowe, Chuck J<br>
<b>Sent:</b> Monday, August 31, 2009 10:04 AM<br>
<b>To:</b> powerh-l@lists.sowder.com<br>
<b>Subject:</b> Formatting a number in PowerHouse - version 8.49.E</span></p>
</div>
</div>
<p class="EC_MsoNormal"> </p>
<div>
<p class="EC_MsoNormal"><span style="font-size: 10pt; font-family: 'Arial','sans-serif';">I have a file from an outside vendor. They are sending it pipe delimited "|", There is an amount field that is left justified. Is there anyway to take that character field
and convert it to a numeric field without doing INDEX and bit extracts. Sometimes the field is 123.45 and sometimes 3,123.45. I thought I could use the FORMATNUMBER function but that is for doing the opposite.
</span></p>
</div>
<div>
<p class="EC_MsoNormal"> </p>
</div>
<div>
<p class="EC_MsoNormal"><span style="font-size: 10pt; font-family: 'Arial','sans-serif';">The vendor states they can not send it unformatted.
</span></p>
</div>
<div>
<p class="EC_MsoNormal"> </p>
</div>
<div>
<p class="EC_MsoNormal"><span style="font-size: 10pt; font-family: 'Arial','sans-serif';">Thanks in advance.
</span></p>
</div>
<div>
<p class="EC_MsoNormal"> </p>
</div>
<b><span style="font-size: 10pt; font-family: 'Verdana','sans-serif'; color: rgb(51, 51, 51);">Chuck Lowe
</span></b><b><span style="font-size: 8.5pt; font-family: 'Verdana','sans-serif'; color: rgb(0, 102, 51);">Quest Diagnostics</span></b><span style="font-family: 'Verdana','sans-serif';">
</span><span style="font-size: 8.5pt; font-family: 'Verdana','sans-serif'; color: rgb(102, 102, 102);">| Sr. Programmer/Analyst,SYS Billing System/Denver| 400 Egypt Rd. | West Norriton, PA 19403 USA |
<strong><span style="font-family: 'Verdana','sans-serif';">phone</span></strong> 610-650-6679|
<strong><span style="font-family: 'Verdana','sans-serif';">fax</span></strong> 610-650-2111 | <a href="mailto:Chuck.J.Lowe@questdiagnostics.com"><span style="color: rgb(102, 102, 102);">Chuck.J.Lowe@questdiagnostics.com</span></a> |
<a href="http://www.questdiagnostics.com/"><span style="color: rgb(102, 102, 102);">www.QuestDiagnostics.com</span></a>
</span><span style="font-family: 'Verdana','sans-serif';"></span><br>
<p style="margin-bottom: 48pt;"><span style="font-size: 8.5pt; font-family: 'Verdana','sans-serif'; color: rgb(0, 102, 51);">Please think about resource conservation before you print this message</span></p>
<div>
<p class="EC_MsoNormal"> </p>
</div>
<p class="EC_MsoNormal"><br>
------------------------------------------ <br>
The contents of this message, together with any attachments, are intended only for the use of the person(s) to which they are addressed and may contain confidential and/or privileged information. Further, any medical information herein is confidential and protected
by law. It is unlawful for unauthorized persons to use, review, copy, disclose, or disseminate confidential medical information. If you are not the intended recipient, immediately advise the sender and delete this message and any attachments. Any distribution,
or copying of this message, or any attachment, is prohibited.</p>
</div>
<br>
<hr>
<font size="1" color="Gray" face="Arial"><br>
<br>
NOTICE - This message and any attached files may contain information that is confidential, legally privileged or proprietary. It is intended only for use by the intended recipient. If you are not the intended recipient or the person responsible for delivering
the message to the intended recipient, be advised that you have received this message in error. Any dissemination, copying, use or re-transmission of this message or attachment, or the disclosure of any information therein, is strictly forbidden. BlueScope
Steel Limited does not represent or guarantee that this message or attachment is free of errors, virus or interference.<br>
<br>
If you have received this message in error please notify the sender immediately and delete the message. Any views expressed in this email are not necessarily the views of BlueScope Steel Limited.<br>
</font><br /><hr />Click less, mail more: Hotmail on the new <a href='http://go.microsoft.com/?linkid=9677402' target='_new'>MSN homepage!</a></body>
</html>