• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

custom value


Active Member
Perhaps Data - Text to columns ( if you post a sheet with some data and expected outcome it would help)


Excel Ninja
jiwan singh
As a new member, You should reread Forum Rules:
  • PLEASE DON'T SHOUT! We have big ears and will hear you just the same.

    What would to do with others than pcs?

Peter Bartholomew

Well-Known Member
The value part is easy since all you have in the original range is the number. The units are created by number formatting. Since the number formats do not change the simplest solution would be to create the units column manually, after all, someone has already put in considerably more work to create the number formats. The first formula is
= amount
but this time using a simple number format. The units of measure can be read using a UDF
= units(amount)
where the UDF is
Option Explicit
Option Base 1

Function units(amount As Range)
Dim s As String
Dim v(10, 1) As String
Dim i As Long
For i = 1 To 10
    s = amount.Item(i).Text
    v(i, 1) = Right(s, 3)
Next i
units = v
End Function
I doubt it is worth the effort though.


Excel Ninja

1] E2, formula copy down :


>> Format cell in "General"

2] Select F2 >> Define name >>

>> Name : ExtractUnit
>> Refers to : =GET.CELL(53,C2)
>> OK


In F2, formula copied down :

=MID(ExtractUnit,FIND(" ",ExtractUnit)+1,99)

>> File saved in "xlsm" type "Excel Macro-Enabled Worksheet"