Unit of measure in number format

[falconeyes]

I have a worksheet in which cell contains both numbers and texts. The cells have units of measurement attached to number and these UOM are included in number format.

For example, the cell has 2.0 kg. The format for the cell is custom and
reads #,##0.0" KG";-#,##0.0" KG";#,##0.0;@.

Kindly guide me as I want to extract the UOM from the values.


Regards

Ahmad

 

[Marcol]

If the cells are all formatted

#,##0.0" KG";-#,##0.0" KG";#,##0.0;@

Why do you need to extract the UOM, it can only be "KG"
If your Data is in A2 down then you could try in B2 (I can't see why though)

=IF(ISNUMBER(A2),"Kg","")

However if the cells are individually formatted with different UOMs then you will need a UDF
In a standard Module

Function ExtractUOM(rng As Range) As String
    Dim strTemp As String
    If IsNumeric(rng) Then
        strTemp = WorksheetFunction.Substitute(rng.NumberFormat, Chr(34), "|", 2)
        strTemp = Trim(Mid(strTemp, InStr(1, strTemp, Chr(34)) + 1, InStr(1, strTemp, "|") - InStr(1, strTemp, Chr(34)) - 1))
    End If
    ExtractUOM = strTemp
End Function

Use in B2

=ExtractUOM(A2)

Drag/Fill Down