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.

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

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

Zero fill, no decimal, need postive/negative sign
 

try:

=TEXT(100*ABS(F3),"000000000000000")&IF(F3<0,"-","+")

wrote:

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

--

Dave Peterson

Zero fill, no decimal, need postive/negative sign
 

That works!
Thanks very much, I really appreciate your help.
Have a good day.