left fill a negative number

[rptlady]

I'm saving a file as a space delimited (.prn) for a preprocessor. I have used the rept function to left fill my numbers to 15 characters. Only problem is if the number is a negative, it looks like this

000000000-42.00

Any suggestions?

Thanks

 

[bluedragon2]

This is simple, but may not work in your instance:

format the cell as -000000000000.00 under custom and in the cell put -1*A1

Assumeing a1 is original data

 

[bluedragon2]

opps, add another 0 for 15

 

[Zathras]

How do you want negative numbers to appear for the follow-on process?

-00000000042.00
or
00000000042.00-
or
000000000042.0}

If you post the code you are using, I can tweak it for you.

 

[rptlady]

Zathras - thank you

I need it to be -00000000000.00

I'm assuming though that if I have to have the -, it also has to count as a character.

Any help will be appreciated

 

[rptlady]

bluedragon2

Thanks, I'll try it all.

 

[bluedragon2]

Might try this:

=IF(A1<0,(REPT(0,11)&A1*-1)*-1,REPT(0,11)&A1)

 

[Zathras]

Ok, so if your number is in B6, for example, you can use this:
[blue]

  =TEXT(B6,IF(B6>0,"0","")&"00000000000.00")

[/color]

 

[rptlady]

Zathras - thanks but that didn't work because it has to be read as a number. Plus when it's a negative it comes out to 16 characters and it has to be 15.

this may make more sense to you.

This is the formula I have

=REPT(0,15-LEN(FIXED('Usable Tables'!E4,2,TRUE)))& FIXED('Usable Tables'!E4,2,TRUE)

This is the result when it’s a negative

0000000000-42.00

This is the desired result

-00000000042.00

Any help is appreciated

 

[bluedragon2]

Will this get you what you want:

=IF(A1<0,&quot;-&quot;&REPT(0,15-LEN(FIXED((A1*-1),2,TRUE)))& FIXED((A1*-1),2,TRUE),REPT(0,15-LEN(FIXED(A1,2,TRUE)))& FIXED(A1,2,TRUE))

A1 is original number.

 

[Zathras]

rptlady: I am confused. The formula I posted produces a 15-character string either
[blue]

  000000000042.00

[/color]
or
[blue]

  -00000000042.00

[/color]
depending on whether the value in 'Usable Tables'!E4 is 42 or -42, which is what I thought you were asking for. Both forms (positive and negative) can be read as a number. Try putting in a formula to add 1 and see that you get 43 or -41 according to the original number.

Here is the formula again, this time using your sheet names:
[blue]

  =TEXT('Usable Tables'!E4,IF('Usable Tables'!E4>0,"0","")&"00000000000.00")

[/color]
Did you actually try it, or are you put off by the use of the TEXT function?


bluedragon2: Your last post produces a 16-character string when the input is -42. I'm not trying to hijack the thread, our original posts were made within seconds of each other.

 

[rptlady]

Zathras and Bluedragon2

Zathras, I did try it, I just tried it again and it seemed to work this time. I just have to test the upload into the preprocessor now. Must have typed something wrong the 1st time. (It was a fat finger day)

Thanks a bunch both of you.