Sign up ×
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


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:


where I expected it to be


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 '14 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. – Alexander Truemper Apr 29 '14 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 '14 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:


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. – Alexander Truemper Apr 29 '14 at 9:34
There is the (german) tool I use to convert function names from french to english ;) – ssssteffff Apr 29 '14 at 9:35
Result: =WIEDERHOLEN(0;24-(LÄNGE(Konstante!$B$5)+LÄNGE(Eingabe!$D2)))&Konstante!$B$5&Ei‌​ngabe!$D2 – ssssteffff Apr 29 '14 at 9:36
Thank you @ssssteffff your solution works for me :-))) happy – Alexander Truemper Apr 29 '14 at 12:20

Your Answer


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.