• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Format cells to show first six numbers (not digits) only - ignore decimal points


New Member
Hi all,

I've put together a matrix for currency rates and naturally I want column width to be standard across the range. By extension then, I need to retain a standard character length for FEX rates i.e. only show the first six numerical digits, regardless of decimal places.

At present, I've formatted cells as below:
_(* #,##0.00000_);[Red]_(* (#,##0.00000);_(* "-"_);_(@_)

By way of example, the above formatting generates the below results for the two different currency pairs (one strong pair e.g. GBP:USD and one weak e.g. USD : IRR):
GBP : USD - 1.15818
USD : IRR - 42,350.91551

Because of the formatting instruction, there are five numerical digits after the decimal point, whereas I want to limit the entire FEX rate to six digits, regardless of decimal point position i.e.
GBP : USD 1.15818
USD : IRR 42,350.9

Can anyone advise on how to do this? My feeling is that I'm overlooking something very simple, but I haven't found the answer yet...

Peter Bartholomew

Well-Known Member
Instead of formatting the number, I think you could round the rates to 6 digits and then use 'General' as the number format
= LET(
  numDigits, 5 - INT(LOG10(FEX.rates)),
  ROUND(FEX.rates, numDigits)
Without Excel 365, you would perform the same task by nesting the formula.