Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Zero fill, no decimal, need postive/negative sign
Hi all,
I am trying to format a number field so it can be exported to a text file for import. I need to format numbers and remove the decimal (if it has one), zero fill to the left (field is 15 characters) and include a positive or negative sign on the far right. The 15th character is the +/- sign. Examples: 1234.00 should be 00000000123400+ 10.25 should be 00000000001025+ 12 should be 00000000001200+ -845.27 should be 00000000084527- I seem to have the formatting correct except for the +/- sign I use: =TEXT(100*F3,"000000000000000") to get the numbers formatted The problem is the +/- sign. I thought about using an adjacent field with: =IF(F3 0,"+","-") which give me the +/- sign next to the number, which is usable. My problem with this is with negative numbers (like -845.27). The field that contains the re-formatted number (using the =TEXT command above) still include the negative sign. So when the 2 cells are put together I get: 00000000-84527- Any ideas? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Zero fill, no decimal, need postive/negative sign
=TEXT(100*F3,"000000000000000")&IF(F3<0,"-","+")
What happens with 0? rdraider wrote: Hi all, I am trying to format a number field so it can be exported to a text file for import. I need to format numbers and remove the decimal (if it has one), zero fill to the left (field is 15 characters) and include a positive or negative sign on the far right. The 15th character is the +/- sign. Examples: 1234.00 should be 00000000123400+ 10.25 should be 00000000001025+ 12 should be 00000000001200+ -845.27 should be 00000000084527- I seem to have the formatting correct except for the +/- sign I use: =TEXT(100*F3,"000000000000000") to get the numbers formatted The problem is the +/- sign. I thought about using an adjacent field with: =IF(F3 0,"+","-") which give me the +/- sign next to the number, which is usable. My problem with this is with negative numbers (like -845.27). The field that contains the re-formatted number (using the =TEXT command above) still include the negative sign. So when the 2 cells are put together I get: 00000000-84527- Any ideas? Thanks in advance. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Zero fill, no decimal, need postive/negative sign
Thanks Dave,
This is getting closer. I still get a duplicate negative sign for any negative amounts. Example: -845.27 ends up -000000000085291- How can I get rid of the leading negative sign. I have tried formatting the source cell various ways but can't quite get it right. Zero works fine but should never happen here as this is for expense reports (why submit a zero expense). Thanks for your help. Dave Peterson wrote: =TEXT(100*F3,"000000000000000")&IF(F3<0,"-","+") What happens with 0? rdraider wrote: Hi all, I am trying to format a number field so it can be exported to a text file for import. I need to format numbers and remove the decimal (if it has one), zero fill to the left (field is 15 characters) and include a positive or negative sign on the far right. The 15th character is the +/- sign. Examples: 1234.00 should be 00000000123400+ 10.25 should be 00000000001025+ 12 should be 00000000001200+ -845.27 should be 00000000084527- I seem to have the formatting correct except for the +/- sign I use: =TEXT(100*F3,"000000000000000") to get the numbers formatted The problem is the +/- sign. I thought about using an adjacent field with: =IF(F3 0,"+","-") which give me the +/- sign next to the number, which is usable. My problem with this is with negative numbers (like -845.27). The field that contains the re-formatted number (using the =TEXT command above) still include the negative sign. So when the 2 cells are put together I get: 00000000-84527- Any ideas? Thanks in advance. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Zero fill, no decimal, need postive/negative sign
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Zero fill, no decimal, need postive/negative sign
That works!
Thanks very much, I really appreciate your help. Have a good day. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Postive # converted to Negative # | Excel Worksheet Functions | |||
Can I sort cells of postive and negative numbers in absolute order | Excel Discussion (Misc queries) | |||
Showing Negative as Postive | Excel Discussion (Misc queries) | |||
Automate bar chart color based on value postive or negative | Excel Discussion (Misc queries) | |||
reversing signs - making postive a negative number | Excel Worksheet Functions |