Format number for fixed length field

[gcf1956]

Dear Helper,

I have a 7-character field, which must receive a number.  The length of the field is fixed: 7 characters.  For instance, the field is located from column 10 to 16 (inclusive) as in the following example (imagine an Hollerith card!!!) where the field is shown with equal signs (i.e. =).

123456789======7890123

The first character must be a '-' (i.e. minus sign) for a negative number, or a space for a positive number.  the number can be any real number, positive or negative.  The number must be rounded correctly.

Let's take an example: 347.2952.  It should result in

123456789 347.307890123

Note that the number has been rounded correctly.  If the number was negative, it would be

123456789-347.307890123

If the number was 1., the result would be

123456789 1.00007890123

Note the result does not need to be padded with zero to the right.

Here is another example: 0.3472952.  The result would be

123456789 0.34737890123

or

123456789 .3472307890123

which would be even better since it carry potentially more precision.

I am seeking the simplest solution with a combination of TEXT, LEFT, RIGHT, CONCATENATE, IF, etc. functions.

Thank you for your help.

gcf1956

[#2 · Accepted solution]

@gcf1956 

Lets say you have a number such as 347.2952 in cell A1.

Enter the following formula in B1:

="123456789"&TEXT(A1,IF(A1<0,""," ")&REPT("0",MAX(INT(LOG(ABS(A1)))+1,0))&"."&REPT("0",5-MAX(INT(LOG(ABS(A1)))+1,0)))&"7890123"

This can be filled down.

Warning: the number in A1 must have at most 5 digits before the decimal point.

12345.678 will work, but 123456.78 won't.

[#3]

@Hans Vogelaar Thank you Hans for your quick response.  Yes, it works.  To be honest, I am a bit disappointed.  Actually, I came up to the same conclusion: logs would resolve the issue.  It is hard to believe that there is no simpler solution for fixed length field, which was the norm some (long) times ago.

Actually, I mixed two things in my question.  I wanted positive and negative numbers to be rounded the same way.  This particular request can be resolved by testing the sign.  (In my application, the sum of all numbers must be equal to zero without any residual.)  The second part of the question is related to the fixed length field and here again for me the use of LEFT or RIGHT, TEXT and CONCATENATE is required, I think.  It is too bad that Excel does not have a simpler solution.  Of course, there is always VBA.

If you have some second thought, it would be appreciated.  In any case, thank you very much for your working solution.

Regards,

gcf1956

PS: 60 years ago, my neighbor's name was Vogelaar way back then when I lived in Uccle, Belgium.

[#4]

@gcf1956 

I remember the time when fixed-length fields were standard, but as you have found, Excel does not have built-in support for it. A custom VBA function would be the alternative; the cell formula would look less messy.