Pad number (that comes from a formula) with zeros (before and after)

[PeteWright]

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:
= 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

 

[hagia_sofia]

As far as the format is concerned, would TEXT function work for you:

=TEXT(A1,"000.000")

(instead of A1 insert your data or formula)

As far as the blanks are concerned, something like this:

=IF(SUM(--(NOT(ISBLANK(A1:E1))))<>COLUMNS(A1:E1),"","SOME FORMULA")

 

[Sektor]

Another option is to use regular expressions in Excel (bp=before point, ap=afer point):

=LET(bp;REGEXEXTRACT(A1;"^[0-9]{1,3}(?=\.)?");
     ap;IFERROR(REGEXEXTRACT(A1;"(?<=\.)[0-9]{1,3}$");"");
     REPT("0";3-LEN(bp))&bp&"."&ap&REPT("0";3-LEN(ap)))

 

[Phuoc]

Try this;

'= IF(OR(A1:E1 <>"");"_SOME_FORMULA_";"")

 

[PeteWright]

= TEXT(A1,"000.000")

Nice, didn't know that one.

= IF(OR(A1:E1<>"");"_SOME_FORMULA_";"")

Didn't know that OR accepts a Range too, thanks. Learned something new.

Many thanks @hagia_sofia and @Phuoc with your suggestions I made up a pretty short formula

= IF(OR($A1:$E1<>"");TEXT(D1;"000.000");"")

which does exactly what I need.

Another option is to use regular expressions in Excel

Thanks for the idea, but I think I'll stick with what I have now since it's shorter

 

[hagia_sofia]

We are glad to help!