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

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

32.75

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.

Best Practices

Accepted answers

MarqueeCrew

@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

All comments

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.

solution.yxmd

MarqueeCrew

@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.

workflow results results

How do I pad zeros on a fixed decimal with negatives.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703