# custom value

#### jiwan singh

##### New Member
in excell - custom value ""0"pcs" , how to get number & pcs in separate coloum

#### pecoflyer

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

#### vletm

##### Excel Ninja
jiwan singh
... Modify Your custom value as "0" and write pcs in separate column.

#### pecoflyer

##### Active Member
Ah yes, I hadn't understood the request

#### jiwan singh

##### New Member
PLEASE SEE THE ATTACHED FILE- I WANT SEPARATE VALUE NUMBER & PCS

#### Attachments

• 9.9 KB Views: 6

#### vletm

##### 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
Code:
``````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.

#### bosco_yip

##### Excel Ninja
Try,

1] E2, formula copy down :

=C2

>> Format cell in "General"

2] Select F2 >> Define name >>

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

Then

In F2, formula copied down :

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

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

#### Attachments

• 12.2 KB Views: 5

#### Peter Bartholomew

##### Well-Known Member
@bosco_yip
Good thinking! I did consider .NumberFormat rather than .Text but I tend to forget about the old xl macro commands, simply a case of lacking familiarity.