PxPlus User Forum

Twitter Twitter Twitter

Author Topic: Need leading zeros in excel  (Read 973 times)

steven.rajski

  • Member
  • **
  • Posts: 12
    • View Profile
Need leading zeros in excel
« on: April 27, 2022, 09:28:16 AM »
I am outputting my line in the following IOLIST but I need to show the leading zeroes, especially for PN$:[STR(",")].  Line 55040 works great except leading zeroes are being suppressed when I view the output in Excel.

55040 LINE1:IOLIST PRC$:[STR(",")],PN$:[STR(",")],DUPRESIND$:[STR(",")],ONEPRIC
55040:E$[2]:[STR(",")],ONEPRICE$[3]:[STR(",")],ONEPRICE$[4]:[STR(",")],ONEPRICE
55040:$[5]:[STR(",")],ONEPRICE$[6]:[STR(",")],ONEPRICE$[7]:[STR(",")],ONEPRICE$
55040:[8]:[STR(",")],ONEPRICE$[9]:[STR(",")],ONEPRICE$[10]:[STR(",")],ONEPRICE$
55040:[11]:[STR(",")],ONEPRICE$[12]:[STR(",")],ONEPRICE$[13]:[STR(",")],ONEPRIC
55040:E$[14]:[STR(",")],ONEPRICE$[15]:[STR(",")],ONEPRICE$[16]:[STR(",")],ONEPR
55040:ICE$[17]:[STR(",")],ONEPRICE$[18]:[STR(",")],ONEPRICE$[19]:[STR(",")]
« Last Edit: April 27, 2022, 10:35:21 AM by steven.rajski »

michaelgreer

  • Diamond Member
  • *****
  • Posts: 129
    • View Profile
Re: Need leading zeros in excel
« Reply #1 on: May 06, 2022, 09:41:32 AM »
When using excel UI if you put an apostrophe (hex 27) you get leading 0s displayed with no apostrophe. I have successfully added that to my output to get the desired effect in some cases. Other instances it fails, but it is worth a try.

Mike King

  • Diamond Member
  • *****
  • Posts: 3811
  • Mike King
    • View Profile
    • BBSysco Consulting
Re: Need leading zeros in excel
« Reply #2 on: May 06, 2022, 02:14:41 PM »
The issue is that when Excel is passed text it will analyze the input to see if it might be a number;  If so the cell value will be marked as a number otherwise it would be marked as a string. 

You can see this by simply loading Excel, click on a cell, and enter values such as 007, 0123456, 0000001 -- all will have there leading zeroes stripped. 

You may be able to override this by prefixing the value with a leading apostrophe as is described in the last option on:

https://support.microsoft.com/en-us/office/keeping-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7
 
Mike King
President - BBSysco Consulting
eMail: mike.king@bbsysco.com

MikeinFL

  • Silver Member
  • ***
  • Posts: 35
    • View Profile
Re: Need leading zeros in excel
« Reply #3 on: May 11, 2022, 01:40:30 PM »
When using excel UI if you put an apostrophe (hex 27) you get leading 0s displayed with no apostrophe. I have successfully added that to my output to get the desired effect in some cases. Other instances it fails, but it is worth a try.

I was just having the same issue.   I send something like "0055" to OpenOffice spreadsheet but cell displays "55" and becomes a number.   Your solution works for me in OpenOffice.  Now the cell displays "0055" without an apostrophe and the cell type is detected as text, not numeric, because it will not calculate "0055" as 55.  Which is ok since it's text data.

Thanks!