Trailing zeros in Excel

[alo2009]

Hi,

I have a sheet that gets loaded into SAP through the GLSU add-on and the numbers must be 2-decimal places and must be numbers but for example, Excel takes 168.90 and cuts off the zero to 168.9. Changing the number format just changes the display, not the actual number. I've searched for a solution and the common one is =TEXT(A2,"0.00") but this doesn't work as it needs to be loaded into SAP as a number not text (the GLSU add-on also does a calculation that makes sure the numbers balance to zero so the values need to be numbers and not text).

I have looked at pages and pages of possible solutions but no luck. Anyone out there have a solution?

Thank you!

 

[JonRowland]

Try =text(A2,"#.00")

Jon

 

[arkusM]

Hi,

I have a sheet that gets loaded into SAP through the GLSU add-on and the numbers must be 2-decimal places and must be numbers but for example, Excel takes 168.90 and cuts off the zero to 168.9. Changing the number format just changes the display, not the actual number. I've searched for a solution and the common one is =TEXT(A2,"0.00") but this doesn't work as it needs to be loaded into SAP as a number not text (the GLSU add-on also does a calculation that makes sure the numbers balance to zero so the values need to be numbers and not text).

I have looked at pages and pages of possible solutions but no luck. Anyone out there have a solution?

Thank you!

I am not sure you will get excel to have the trailing zero to be anything other than a display or text. Because as a number 168.90 is 168.9
Do you need to upload .xls or can you try a .csv or .txt? These shoudl allow you to have the zero.

 

[alo2009]

Hi Jon. This didn't work and still shows up as text.

Hi arkusM. It does need to load as .xls. Go Flames go!

 

[arkusM]

Go Flames go!

ahh, you must be in Calgary??? LOL

The problem you are having is odd. And I am not sure I can make sense of it. But that is me, I am far and away from being a expert user!!

Have you successfully used this GLSU before on another sheet?

If so then maybe try and find out what format that sheet used.
Also do you have access to the documentation for the GLSU?
I tried to go onto the GLSU website and it wanted me to sign up for support, but we don't use SAP so there would be little point...
See if you can get help with the format here: http://glsu.com/zglsu.htm


Cheers


Go Flames Go!!

 

[russellrichter]

Have you tried using the ROUND() function? =ROUND(168.9,2) results in 168.90 appearing in the cell. I just don't know how SAP will interpret that. Thought it would be worth a shot, though.

 

[alo2009]

GLSU works fine except for the values that have the trailing zero. The work around has been these are entered manually. Last month we had 2 values with trailing zeros and they just weren't loaded. Like you mentioned, the GLSU website is pretty uninformative. I'll have to dig and find out who in the company deals with GLSU issues.

Russell, I did try the round and it works great for everything else but values with trailing zeros. 12.345 gets rounded to 12.35 but 12.300 gets rounded to 12.3 (12.30 is displayed but in the cell it's 12.3)

 

[arkusM]

GLSU works fine except for the values that have the trailing zero. The work around has been these are entered manually. Last month we had 2 values with trailing zeros and they just weren't loaded. Like you mentioned, the GLSU website is pretty uninformative. I'll have to dig and find out who in the company deals with GLSU issues.

Russell, I did try the round and it works great for everything else but values with trailing zeros. 12.345 gets rounded to 12.35 but 12.300 gets rounded to 12.3 (12.30 is displayed but in the cell it's 12.3)

Where are you manually entering the trailing zero? In SAP?
I am thinking that this is an issue wil GLSU because as far as I can figure, a trailing zero is cosmetic. But I am not math wizard.

Every time I can think about entering a number with a trailing zero, without formating, excel removes the trailing zero as is has no mathmatical significance.

 

[russellrichter]

Try this. Put this line in the Working Storage Section of your Data Division:

01 YourNumber PIC 9(5)V99 VALUE ZEROS.

Oh, wait. That's COBOL. Would've worked, but never mind. :0)