Google Sheets Custom Number Format – Unchanging Numbers Below 1000

Your entry is being interpreted as a Latvian currency value.

Would not happen if you switched to new Google Sheets. Meanwhile your solution is about as good as it gets.

-1,000 Lats puts you out of pocket by around USD1,800 ;-)

Look into the following functions: IF and CONCATENATE.

Taking the frequency as an example: you have one cell, B23 in this example, that contains your "raw" number. 2.2 GHz would be entered as 2200000000. Your sheet will be sortable by this column.

Then in C23, put this formula:

=if(B23>=1000000000,B23/1000000000,if(B23>=1000000,B23/1000000,if(B23>=1000,B23/1000,B23)))

In D23, this formula:

=if(B23>=1000000000,"GHz",if(B23>=1000000,"MHz",if(B23>=1000,"kHz","Hz")))

And in E23, this formula, which produces your formatted value:

=concatenate(C23," ",D23)

You don't actually need three cells. You can have just one cell, putting the IF functions in the parameters of the CONCATENATE function:

=concatenate(if(B23>=1000000000,B23/1000000000,if(B23>=1000000,B23/1000000,if(B23>=1000,B23/1000,B23)))," ", if(B23>=1000000000,"GHz",if(B23>=1000000,"MHz",if(B23>=1000,"kHz","Hz"))))

Using three cells just makes it easier to keep things straight, especially the first few times you use nested functions.

For values using binary prefixes (traditionally used for memory sizes), the factors change to 1024, 1024 squared, etc. Note that the prefix for 1000 is lowercase "k" while for 1024 it is uppercase "K"; M and G are always uppercase.