Custom format string from cell value

[Raj]

Hi,

I need to display units eg. Kg, mm, tonnes etc in a cell. I have
custom formatted the cell as follows for kgs:
0.00 "kg" for displaying kg. As a result, the number 925 is displayed
as 925 kg.

I wish to store the "kg" in a cell and use that value to form the
custom format. Is there any way to do this? Because there are many
cells in the sheet that use the format, and every time I have to
format all the cells with the new unit to be displayed.

Thanks in Advance for the help.

Regards,
Raj

 

[Dave Peterson]

You could create a macro that would loop through all the cells and look at the
number format for each of those cells and modify them if the current number
format matched what you wanted to change.

But that's a lot of work for you and excel.

Instead, I'd create a Style that used the format that I wanted. Then I could
change the style whenever I wanted.

I'd have to assign each of the cells I wanted to behave this way to this style.
But after that, I just change the style and all the cells change.

If you want to try...

In xl2003 menus:
Format|Style
Type the Style name in that combobox at the top
(I used UnitStyle, but you should use something you like)

Then click the modify button.
On the Number tab, you can use a custom format of: 0.00 "kg"
(You can also change all that other formatting if you want.)

Then select your range that should have this style.
Then Format|Style, choose UnitStyle
and click ok.

Now if you want to change the number format for any of those cells that are
using the style, you can just change that style:

Format|Style
select UnitStyle
Click modify
and make your change

Remember that Styles live in workbooks--not the excel application. You'll need
to add this UnitStyle to each workbook that you want to have it.

=========
If you wanted a macro to change the number format, then I'd type the complete
number format in a cell (say A1 of Sheet1).
0.00 "lb"
(for example)

Then you could use a macro to make the change:

Option Explicit
Sub testme()
With ThisWorkbook
.Styles("UnitStyle").NumberFormat _
= .Worksheets("Sheet1").Range("a1").Value
End With
End Sub

Personally, I wouldn't bother with the macro -- I'd just do it manually.

 

[James Ravenswood]

Hi,

I need to display units eg. Kg, mm, tonnes etc  in a cell. I have
custom formatted the cell as follows for kgs:
0.00 "kg" for displaying kg. As a result, the number 925 is displayed
as 925 kg.

I wish to store the "kg" in a cell and use that value to form the
custom format. Is there any way to do this? Because there are many
cells in the sheet that use the format, and every time I have to
format all the cells with the new unit to be displayed.

Thanks in Advance for the help.

Regards,
Raj

First enter the following User Defined Function:

Function WSWG(r As Range) As String
WSWG = r.Text
End Function

Then if you have data in A1:
=RIGHT(wswg(A1),2)
will display the kg in a form that can be tested