Excel formula to return a cell's custom format

excel, excel-formula

Solution

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)`

Problem

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 ```

Original source