Formula for full custom type

[chris1ripple]

Hi All
I need a way of returning the full "custom type" of a cell's format. In my data set, the custom type varies line by line (because the software that exports the data is awful). They are all of type #,##0.00 and then have a different text value at the end. End goal - the data I'm after is file size and is exported as a number with a custom format to add the GB, MB, KB etc. (eg or example #,##0.00 "GB", #,##0.00 "MB", #,##0.00 "KB"). I need the "GB" etc returning so I can convert each cell to KB. I thought I could use =CELL("format", A2) but that doesn't give me enough detail.
Any suggestions?

[XLent]

You could use the old GET.CELL call, via Named Range, but you'd have to enable macros to make it work so I'd say you may as well use a custom UDF, e.g.:

Please Login or Register  to view this content.

above should be stored in a standard module in VBE, called from your cell along lines of:

Please Login or Register  to view this content.

if you prefer, you could modify the Function to handle / aggregate all of the cells in the source range in one go.

edit: tweaked to handle Bytes as missed that... modify multiplier as you need (e.g. 1024 vs 1000)

[Keebellah]

If you want to make it all one measurement like KB the all you need to do is extract the number part of the cell and then depending on the text passed GB, MB of KB multiply accordingly
KB by 1, GB by 1204 or whatever etc.
Use a helper column to do that.

Please Login or Register  to view this content.

Place the above code in a common VBA module and you can use it in a formula or in VBA code

[Fluff13]

In the name manager create a new named range & call it CellFormat, then in the refers to box put

Please Login or Register  to view this content.

and save it.
Then in B2 put

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

The file will need to be saved as an xlsm file

[chris1ripple]

Spot on, that's the easy fix i needed - thank you!

[chris1ripple]

Spot on, that's the easy fix i needed - thank you!

[Fluff13]

Not sure you you're responding to, but glad we could help & thanks for the feedback

[chris1ripple]

Fluff13, I was responding to you - thank you.