Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
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 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)
units = v