CODEWITHSUNDEEP
excel
DisneylandSC
DisneylandSC

Reputation: 966

Obtain custom cell format notation as text

I have a column containing quantities. Along with these quantities there is a unit of measure displayed, such as KG, or L. However this isn't hard copied text but rather some custom formatting rule, for example #.##0 "L". What I would like is to extract these UOMs into their own seperate column as text. Is there some way I can access these formatting rules to do this or is there some other method that will get me what I want?

My solution for completeness.

Function getcellformat(ByRef valuecell As Range) As String

    getcellformat = Replace(Split(valuecell.NumberFormat, " ")(1), Chr(34), "")

End Function

Thanks to teylyn for pointing out the .NumberFormat

Upvotes: 0

Views: 24

Answers (1)

teylyn
teylyn

Reputation: 35915

You can use the .NumberFormat property to show the number format applied to a specific cell

Here's a screenshot from the Immediate Window for the Number Format of cell A1 of the current sheet

enter image description here

Upvotes: 1

Related Questions