Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel worksheet that has three pages:

  1. "Eingaben" / Input - the user types in some data
  2. "Export" - the data is formatted and padded for PRN file Export
  3. "Konstante" - contains some constant values to be used in export tab

On page one there is a field that contains a number (formatting in input as "Standard"). The given number is "67922200305"

In Export the formula says

=TEXT(Konstante!$B$5&Eingabe!$D2;"000000000000000000000000")

so the input value is supposed to be prefixed with "60308700" and the whole thing shall be zero-padded to the length of 24.

Strangely the output in Export is:

000006030870067922200000

where I expected it to be

000006030870067922200305

The format of the export field is Standard and the constant is Text.

To me it looks like "rounding" issue, though I guess '&' should concatenate the strings.

share|improve this question
    
It seems that you have setting which turns input values divided by 1000. So the 305 at the end rounds up. Try TEXT(Konstante!$B$5&TEXT(Eingabe!$D2);"000000000000000000000000"). –  Danatela Apr 29 at 6:47
    
It does not work as the second parameter to the inner TEXT() function is missing. So I tried =TEXT(Konstante!$B$5&TEXT(Eingabe!$D2;"00000000000");"000000000000000000000000") with the same result, it does not display the last three digits. –  AlexTr Apr 29 at 6:58
    
Try to find the setting. I don't know how does it look like in German. It should be on Additional settings tab though. –  Danatela Apr 29 at 7:02

1 Answer 1

up vote 4 down vote accepted

TEXT function takes a numeric value as first parameter, so the result of the concatenation (ie. Konstante!$B$5&Eingabe!$D2) is converted to a number. Note that the same happens to comment suggestion (Konstante!$B$5&TEXT(Eingabe!$D2))).

Take a look at this Wikipedia entry:

Although Excel can display 30 decimal points, its precision for a specified number is confined to 15 significant figures, and calculations may have an accuracy that is even less due to three issues: round off, truncation, and binary storage.

Your number (6030870067922200305) is too long, and Excel keeps only 15 significant numbers (ie 603087006792220).

You can achieve what you are trying to do, dealing with text values exclusively. For example, you could use LEN and REPT functions:

=REPT(0;24-(LEN(Konstante!$B$5)+LEN(Eingabe!$D2)))&Konstante!$B$5&Eingabe!$D2

Here, I repeat 0 to reach 24 (less constant and input lengths), then I concat constant and input values.

Hope this helps.

share|improve this answer
    
thanks, this looks promising, I will need to translate the function names to german, I will give you feedback soon. –  AlexTr Apr 29 at 9:34
    
There is the (german) tool I use to convert function names from french to english ;) excel-translator.de –  ssssteffff Apr 29 at 9:35
1  
Result: =WIEDERHOLEN(0;24-(LÄNGE(Konstante!$B$5)+LÄNGE(Eingabe!$D2)))&Konstante!$B$5&Ei‌​ngabe!$D2 –  ssssteffff Apr 29 at 9:36
    
Thank you @ssssteffff your solution works for me :-))) happy –  AlexTr Apr 29 at 12:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.