Customer format millions and thousands

[Access Beginner]

Hi All,


I have cells, where the value can can vary from the millions to the tens of thousands.

Do you know of any custome format options, if the value is say 5,100,000 we can show this as 5.1M and if the value is say 320,500 we can show it as 320.5T.

Values are in a dynamic chart, so a user can select a region and the values update, for that particular region. The issue is where some regions can go into the millions, where others will only go into the hundreds of thousand and possibly tens of thousands

These values go into a dynamic chart and showing the value as 5,000,000 is taking up too much real estate. If all the values were in the million, this would not be a problem and I could use s aimpel customer format, however when the values vary as mentioned above, I'm stumped.

Any help/advice appreciated.

Cheers

 

[Maximus Tatius]

Is there a way to do the opposite? For instance, if I have data as 1.5K, but I want it to read 1,500.

The easiest way:

1) Format your cells with such data in them as numbers with the thousands separator checked.
2) Ctrl+H on your selection to find and replace all decimal points with nothing
3) Ctrl+H on your selection to find and replace "K" with "00"

 

[Rick Rothstein]

Unfortunately, I don't think custom cell formatting can convert text to numbers...

That is correct, cell formatting cannot change the underlying value in a cell, it can only change how that value is displayed in the cell itself. As for text values in cells (1.5K would be a text value), cell formatting can only display other text along with the original value... it cannot "see" any numbers within a text value, so it cannot format the number part of a text value.