Extracting units from Custom Format

[x_marx_dspot]

Hi,

Good day!

I have a column of figures with several units attached to it via custom format, e.g. KG, PC, G, CS, etc.

I would like to extract the units onto another column, showing only the units using formula only.

I've tried the =cell("format",XX) function but it seems to return ",0" figures.

Thanks!

 

[Andrew Poulsom]

If the cell has a number format applied to it the underlying value doesn't change so you should be able to refer to it in a formula without any parsing.

 

[x_marx_dspot]

If the cell has a number format applied to it the underlying value doesn't change so you should be able to refer to it in a formula without any parsing.

Hi Andrew,

Thanks for the reply, yes, it has a format applied to it. Would you have a formula to extract the units under the custom format? Thanks!

 

[x_marx_dspot]

Here is a sample table.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Quantity in Figures[/TD]
[TD="align: center"]Unit of Measure[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1,200 KG[/TD]
[TD="align: center"]1,200[/TD]
[TD="align: center"]KG[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]5,000 CS[/TD]
[TD="align: center"]5,000[/TD]
[TD="align: center"]CS[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0.500 G[/TD]
[TD="align: center"]0.500[/TD]
[TD="align: center"]G[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

 

[Andrew Poulsom]

Oh I see. You want to extract the unit of measure not the value. That would require VBA, using the NumberFormat property.

 

[x_marx_dspot]

Oh I see. You want to extract the unit of measure not the value. That would require VBA, using the NumberFormat property.

Yes, extraction of the UOM. would there any option that would not use VBA? thanks!

 

[Fazza]

Well you could use the old GET.CELL function.

Simple explanation to suit data set up in sample, select cell C2 and CTRL-F3 to add defined name "CellContent" with formula =get.cell(53,a2)
Then in cell C2 enter the formula =mid(CellContent,FIND(" ",CellContent)+1,255)

Or some variation of that. Could put it all in a defined name, for example. cheers