PeteWright
Active Member
- Joined
- Dec 20, 2020
- Messages
- 458
- Office Version
- 365
- Platform
- Windows
- Mobile
Hi all!
I have a table (containing a coordinate list) but for further processing I need the number to have the specific format "000.000" (exactly 7 characters long).
Some examples are:
123.456 > no changes
123.4 > pad with zeroes to 123.400
123 > pad with zeroes to 123.000
98.765 > pad with zeroes to 098.765
98 > pad with zeroes to 098.000
The tricky part (at least I think it is) that the number comes from a formula otherwise I'd try something like number format "000.000".
In addition I'd like to display a blank cell instead of a number, if any of a set of given cells are blank.
For example if any of the cells (A1, B1, C1, D1 or E1) is blank display nothing in F1 and the padded number otherwise.
Right now i have a huge formula for this like:
and the actual formula is also quite long which makes it less readable.
Is there a way to get rid of this?
Thanks in advance
Pete
I have a table (containing a coordinate list) but for further processing I need the number to have the specific format "000.000" (exactly 7 characters long).
Some examples are:
123.456 > no changes
123.4 > pad with zeroes to 123.400
123 > pad with zeroes to 123.000
98.765 > pad with zeroes to 098.765
98 > pad with zeroes to 098.000
The tricky part (at least I think it is) that the number comes from a formula otherwise I'd try something like number format "000.000".
In addition I'd like to display a blank cell instead of a number, if any of a set of given cells are blank.
For example if any of the cells (A1, B1, C1, D1 or E1) is blank display nothing in F1 and the padded number otherwise.
Right now i have a huge formula for this like:
= IF(OR(NOT(ISBLANK(A1));NOT(ISBLANK(B1));NOT(ISBLANK(C1));NOT(ISBLANK(D1));NOT(ISBLANK(E1)));"_SOME_FORMULA_";"")
and the actual formula is also quite long which makes it less readable.
Is there a way to get rid of this?
Thanks in advance
Pete