Is there a way to do 5 different number formats in one cell or write VBA script to produce a number format based on the value?

Question

The data I produce has to have specific number formatting which depends on how large or how small the number is.

Numbers >=100 shouldn't have any decimal places, >=10 should have one decimal place (even if it is a zero), >=1 should have two decimal places (even if the last is a zero), 0.999-0.001 should have three decimal places (even if the last is a zero), and 0.0009-0.0001 should have four decimal places, again regardless of what the number in the last decimal place is.

So essentially I need a code, or way, to look at a number and give me those specific decimal places. I know I can format them individually, but we are talking thousands upon thousands of numbers that I would be formatting. I have tried using the round function but it won't give me trailing zeros.

This is currently what I am using as a function on the spreadsheet which works for everything but trailing zeros:

=IF(VLOOKUP($A$21,'Copied Data'!$A$15:$FL$140,23)>'Copied Data'!U$7,IF('Copied Data'!W16<0.001,ROUND('Copied Data'!W16,4),IF('Copied Data'!W16>=0.001,ROUND('Copied Data'!W16,3),IF('Copied Data'!W16>0.999,ROUND('Copied Data'!W16,2),IF('Copied Data'!W16>9.999,ROUND('Copied Data'!W16,1),IF('Copied Data'!W16>99.999,ROUND('Copied Data'!W16,0)," "))))),"<"&IF('Copied Data'!U$7<0.001,ROUND('Copied Data'!U$7,4),IF('Copied Data'!U$7>=0.0001,ROUND('Copied Data'!U$7,3),IF('Copied Data'!U$7>0.999,ROUND('Copied Data'!U$7,2),IF('Copied Data'!U$7>9.999,ROUND('Copied Data'!U$7,1),IF('Copied Data'!U$7>99.999,ROUND('Copied Data'!U$7,0)," "))))))

If there is a better way to do this, or a way to write a macro for it in VBA, that would be great! Thank you.

Answer 1

Something like this will deal with modifiers:

Dim rng As Range, c As Range, op, v

Set rng = ActiveSheet.Range("A1:A100") 'for example, or = Selection

For Each c In rng.Cells
    v = Trim(c.Value)
    If Len(v) > 0 Then
        'if the value has a modifier, remove it
        op = ""
        If v Like ">*" Or v Like "<*" Then
            op = Left(v, 1)
            v = Trim(Right(v, Len(v) - 1))
        End If
    
        If IsNumeric(v) Then
            v = CDbl(v)
            If v > 0.0001 And v <= 0.00099 Then
                c.Value = v
                c.NumberFormat = op & "[format1]" 'include any modifier in the format
            ElseIf v > 0.001 And v <= 0.999 Then
                c.Value = v
                c.NumberFormat = op & "[format2]"
            Else
                'you should probably include a general format so all
                ' cells which might have modifiers get the same treatment
            End If
            'add other cases as needed
        End If
    End If

Next c

Where [format1] etc are the formats you got from your macro recording

Note that any modifiers are no longer part of the value of the cell after this - they exist only in the cell format. That means you can work with the cells as numbers, but depending on what you need to do eventually it might or might not be appropriate.

If you just want to change the format of the numeric part of the cell value, then you could do something like:

c.Value = Application.Text(v, op & "0.0000")