Formatting a mathematical interval in excel

Question

I have a column filled with values like:

   [0,55553656475856 ; -8,34567323546546]

I would like to display them as

   [0,56 ; -8,34 ]

so both numbers displaying only 2 decimal positions. Is there a way to do it in excel (2016) without actually modifying the underlying values or adding extra columns, as these are programmatically read-in later? With a kind of format or so?

No, that is a text string and number format will not touch it. You can use a helper column with a formula like below or vba to do it in place. But both will change the underlying value. — Scott Craner
– Scott Craner, Commented May 2 at 22:20
To preserve the data address for further programmatical use, create a copy of the whole sheet then apply the formula below. — rotabor
– rotabor, Commented May 3 at 5:30

Jack · Accepted Answer · 2025-05-02 22:05:59Z

1

You can use this formula:

="[" &
TEXT(SUBSTITUTE(MID(A1,2,FIND(";",A1)-3),",","."),"0.00") &
" ; " &
TEXT(SUBSTITUTE(LEFT(RIGHT(A1,LEN(A1)-FIND(";",A1)-1),LEN(RIGHT(A1,LEN(A1)-FIND(";",A1)-1))-1),",","."),"0.00") &
"]"

answered May 2 at 22:05

Jack

2,8902 gold badges30 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Formatting a mathematical interval in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related