How can I make this number format possible on Microsoft Excel?

Question

The detail of my problem:

I am using a table to track the size of some files in Byte by the built-in format Number with 1000 separator, example here (Number) 1234 -> (Number with format) 1,234. For my personal style, I want it to be display with a 000TB, 000GB, 000MB, 000KB, 000B format, full example below.

Original number	Number to be display with format (My goal)
111	111B
111222	111KB, 222B
111222333	111MB, 222KB, 333B
111222333444	111GB, 222MB, 333KB, 444B
111222333444555	111TB, 222GB, 333MB, 444KB, 555B

What did I try:

(1) I tried the Format Cells > Number > Custom way so I can keep them in Number for the further calculation but is not perfect for my situation.

Format [>999999999999]#"TB, "000"GB, "000"MB, "000"KB, "000"B";[>999999999]#"GB, "000"MB, "000"KB, "000"B";#"MB, "000"KB, "000"B" is not perfect, good for TB and GB size, not good for MB KB and B size.

Format [>999999999999]#"TB, "000"GB, "000"MB, "000"KB, "000"B";[>999999999]#"GB, "000"MB, "000"KB, "000"B";[>999999]#"MB, "000"KB, "000"B" comes an error.

Yes I know it can only be Positive Value; Negative Value; Zero Value which can only supports for 2 cases and I need 5 cases.

(2) I tried the Formulas > Name Manager > LAMBDA function so they can be display as Text but not possible for further calculation again.

I use this function in my statistics table =Sub.SizeFormat(SUMIFS(Table.List[Size(Byte)],Table.List[Month],[@Month],Table.List[Device],[@Device])).

Name	Refers to
Sub.SizeFormat	=LAMBDA(Para_Size,CHOOSE(ROUNDUP(LEN(Para_Size)/3,0),Sub.SizeB(Para_Size),Sub.SizeKB(Para_Size),Sub.SizeMB(Para_Size),Sub.SizeGB(Para_Size),Sub.SizeTB(Para_Size)))
Sub.SizeB	=LAMBDA(Para_Size,SUBSTITUTE(TEXT(Para_Size,"#,##0."),".","ʙ"))
Sub.SizeKB	=LAMBDA(Para_Size,SUBSTITUTE(SUBSTITUTE(TEXT(Para_Size,"#,##0."),",","ᴋ, ",1),".","ʙ"))
Sub.SizeMB	=LAMBDA(Para_Size,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(Para_Size,"#,##0."),",","ᴍ, ",1),",","ᴋ, ",2),".","ʙ"))
Sub.SizeGB	=LAMBDA(Para_Size,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(Para_Size,"#,##0."),",","ɢ, ",1),",","ᴍ, ",2),",","ᴋ, ",3),".","ʙ"))
Sub.SizeTB	=LAMBDA(Para_Size,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(Para_Size,"#,##0."),",","ᴛ, ",1),",","ɢ, ",2),",","ᴍ, ",3),",","ᴋ, ",4),".","ʙ"))

What am I expecting:

Is there possible for me to insert units TB GB MB KB B according to the number and keeps them in Number for further calculation?

If not possible, better idea for my LAMBDA logic?

Answer 1

Instead of applying the format directly to the cells, use conditional formatting, and apply different formats based on their values: