Impose maximum digits on numbers in cells, regardless of position of decimal point?

Question

I have a spreadsheet with the number of units of different investment funds, and the price per unit of each (in the same row).

Some of these “price per unit” values can have more than 10 decimal places, e.g. 5.6343268597991. Some of the numbers of units can be in the thousands, but also with decimals, e.g. 14858.28.

What I want to do is somehow find a way of rounding all these cells to a specific number of numbers, regardless of where the decimal point happens to be. So if I limited to 6 digits, rounded, the above 2 numbers would become:

5.63433
14858.3
(i.e. both rounded up…)

Any way of doing this? With or without a macro?

Later
Thanks for anyone taking an interest in this, but I have found my solution in LibreOffice Calc's ROUNDSIG function. I have no interest now in an Excel-based solution (and believe there is no ready-made equivalent function).

I realise I did originally include the tag "Microsoft Excel" (now replaced with "spreadsheet").

Answer 1

I also posted this question in a LibreOffice forum (spreadsheet app: Calc).

A function there, ROUNDSIG, does precisely this. No expert in Excel but first search comes up with no equivalent function.

Answer 2

Excel does not have that function, but here is an Excel formula that will do the same thing:

=--TEXT(A1,"."&REPT("0",SigDigits)&"E+000")

*Note that this formula assumes you want to return significant digits, not just six digits.

Answer 3

=ROUND(A1,6-LEN(INT(A1)))

It rounds up to 6 numbers behind the dot minus the number of digits before it.