is there a way to use VBA (or some excel function) to return a custom format that is used?

For example:

1. in cell A1 I have a value of 100 with custom format [$EUR ]#.##0,00;[Red][$EUR ]-#.##0,00
2. in cell A2 I have a value of 100 with custom format [$PLN ]#.##0,00;[Red][$PLN ]-#.##0,00 So basically, in A1 I have red colored EUR -100,00 and in A2 I have red colored PLN -100,00

Now let's assume I want do some calculations with only those cells that are formatted as EUR (in example above it means the cell has [$EUR] inside it's custom format.)

Any ideas on how to get that custom format string or anything else that will help me to differentiate those two cells by their custom format?

I tried Excel formula CELL("format";A1) and CELL("format";A2), but they both return ,2-

I've seen many VBA solutions to format a cell, but I haven't found any VBA function that returnes the custom format used (there are ways to return some general stuff like color, is it number etc, but I didn't find any custom format returns).

CodePudding user response：

If you want to read that cells formatting (custom or not) then use:

Range("A1").NumberFormat

If you want a small UDF, that you can call from the worksheet itself, you need a small amount of VBA:

Create a new module, if you don't have one.

Paste into it the following:

Function formatting(rng As Range) As String
    Application.Volatile
    formatting = rng.Cells(1, 1).NumberFormat
End Function

Note, this will always return the formatting of the first cell within the range.

Then, in any given cell you can call that function- e.g.

=formatting(A1)

From there, you can use normal text manipulation functions such as Left, Mid etc. to locate the colour code or currency symbol - e.g.

=IF(LEFT(formatting(A1),2)="[$",MID(formatting(A1),3,3),"No currency found")

would give the result EUR