CODEWITHSUNDEEP
excelexcel-formula
Chuck The Nerd
Chuck The Nerd

Reputation: 661

Excel formula to return a cell's custom format

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

Upvotes: 4

Views: 17110

Answers (3)

Jim
Jim

Reputation: 11

I have been trying to solve this issue as well.
Possible solution using old Excel 4 macro function.

Press CTRL-F3 to get macro editor up, create new macro and give it a name like ExtractUnits

In the refers to box, enter

=GET.CELL(53,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1))

In the cell to the right of the data (in this case column B), enter

=ExtractUnits

It is really, really slow to calculate, but is getting me the data out as text. Mine is all the same length, so I use right to extract the units, but you could find the space and parse. If you want a different cell, change the offset parameters.

Upvotes: 1

nutsch
nutsch

Reputation: 5962

Build a user-defined function by copying the attached code in your workbook module, you can then use it in your worksheet as =if(iscase(a1),5,1)*a1 

Function IsCase(rgCell As Range) As Boolean
If InStr(UCase(rgCell.NumberFormat), "CASE") > 0 Then
    IsCase = True
Else
    IsCase = False
End If

End Function

Upvotes: 2

gtwebb
gtwebb

Reputation: 3011

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)

Upvotes: 5

Related Questions