Forum Discussion
Format number for fixed length field
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.
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.
HansVogelaar 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.
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.
