I often end up with data in excel about file sizes where values may include things like:
0
1K
850K
7.12K
19.2M
43.1G
1012G
28T
1.02P
(ok, the last one not so often, but it's a theoretical possibility, eventually)
I often want to identify the large or small outliers, sort them in size order, or use conditional formatting to help me see which ones are larger than those around them.
My first 2 attempts were:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"T","000000000000"),"M","000000"),"K","000"),"G","000000000")
=IFERROR(LOG(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"T","000000000000"),"M","000000"),"K","000"),"G","000000000"),10),0)
(the log effectively converts to "number of digits", and iferror handles the zero case)
While imprecise in terms of 1000 vs 1024, this doesn't matter to me as I'm only interested in spotting outliers and the second performs perfectly for my purposes, but it totally falls over when there is a decimal point present.
I'm ideally looking for something quick and hacky that I can copy/paste (or even better type in) when spending 5 mins interpreting logs or reports for anomalies, I'm never intending to save the sheets I'm using, just extract infromation, be informed by it, then move onto the next job.
Highlighting red if it contains a "G" and purple if it contains a "T" would almost be good enough for me, but there's a little too much diferent between 1G and 990G.
Any good sugestions?
If it actually were robust in terms of support for 1024 multiples, and be able to handle MB as will as M, this would be great, but not necisary for my purposes.