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.

=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

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

try:

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

[email protected] 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

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