1

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.

3
  • 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, 2014 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.
    – user118305
    Apr 29, 2014 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, 2014 at 7:02

1 Answer 1

3

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.

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

You must log in to answer this question.