Excel formula to return a cell's custom format

Question

I'm trying to normalize data by reading a custom number format. The actual data in the following cells only have the values 3 and 33

[A1]  3 CASES
[A2]  33 UNITS

The terms "CASES" and "UNITS" are displayed because they are part of the custom format distinct to each cell. e.g. A1 has custom number format code: # "CASES", and A2 has custom number format code: # "UNITS"

I would like to normalize this data via an excel formula into UNITS for reporting purposes. I attempted to use the formula

=CELL("format",A1)

but the value returned is F0 instead of: # "CASES"

If each CASE has 5 units I am expecting to have an additional column that computes NORMALIZED UNITS with a formula that reads something like:

=IF(CELL("format",A1)="CASES",5*A1,A1)

This way the NORMALIZED UNITS in column B would be

[B1]  15
[B2]  33

Answer 1

You could use a custom function.

Function get_format(r As Range) As String

get_format = r.NumberFormat

End Function

Then use

=if(get_format(a1)="# " & CHAR(34) & "CASE" & CHAR(34),5,*A1,1)

Or you could just check to see if the word CASE is in the number format

Function check_case(r As Range) As Boolean

check_case = InStr(1, r.NumberFormat, "CASE")

End Function

=if(check_case(A1),5,*A1,1)