How do I pad zeros on a fixed decimal with negatives?

[Carl_in_CT]

Hello, new here but have lurked a bit and found good info (thank you!) but can't find this solution yet.

I have data that comes from Excel that is in "general" format but I can change the money rows to number or accounting or currency first if that would help, so far it hasn't.  What I need is for amounts to have two decimals, retain the negatives when they are negatives and total 15 characters.  So I have values like this:

175.5

-100

0

32.75

50

And I want them to come out like this:

000000000175.50

00000000-100.00 (but it might need to be -00000000100.00 I won't know until the result file is loaded which way it needs to go)

000000000000.00

000000000032.75

000000000050.00

The result file must be text in notepad, not csv or Excel, and retain that specific format because it has to be loaded into a payroll system from a text file from notepad.

I've tried a variety of pad left and format combinations but I can't seem to get it to pad the zeros and keep the decimals and negatives and get it to stay that way when going to a notepad text output.

[danrh]

Check out the attached. I'm creating two new fields for the two different scenarios you gave, then saving to a comma delimited text file (not a csv). Because your original field is all number, chances are it will be easier to use the Formula tool to create a new field, then a Select tool to de-select your original field and rename your new field to the old field's name (if that makes sense). Play around with it, see if this gets you closer.

[MarqueeCrew · Accepted solution]

@Carl_in_CT,

Here's a formula to create the text numbers:

IF 
	[Field1] >= 0	THEN PadLeft(ToString([Field1],2),20,"0")
ELSE
"-" + PadLeft(ToString(ABS([Field1]),2),19,"0")
ENDIF

Cheers,

Mark

[ponraj]

Here is the sample workflow for your case.  Hope this is helpful. 

workflowresults

[Carl_in_CT]

Thanks, that worked!  I can follow what you did but it would have taken me a very long time to get there myself, if ever.  The only change I made was to change the 20 and 19 to 15 and 14 respectively as Ponraj showed in his workflow.  Thanks to both of you!

[Carl_in_CT]

The only thing I still needed to add was a data cleansing step anywhere before this formula to replace nulls with blanks.  Without doing that nulls turned out as -00000000000000. Now they come out just like when there were actual zeros in the data, 000000000000.00

I just sent a test file to our account management team for them to pass along to our client.  Hopefully it will load properly into their payroll system.  Once we get it to do that I will be training two other people on this and turning this monthly process over to them.

Thanks again for everyone's help!