How to change parts of Cells().NumberFormat
 

Hello there,

I assign Cells().NumberFormat to deal with various currencies.

Initially NumberFormat is "$#,##0.0000", "CHF#,##0.0000", etc.

I would like to keep the currency information, however, I want to have only
2 digits (i.e., 0.00).

Format(NumberFormat), "0.00") does not work.

Any idea how I can cut off the last two digits without loosing the currency
information?

Thank you for your help.

How to change parts of Cells().NumberFormat
 

Do you mean

Msgbox Format(2.5363,"0.00")

If this post helps click Yes
---------------
Jacob Skaria


"FSPH" wrote:

Hello there,

I assign Cells().NumberFormat to deal with various currencies.

Initially NumberFormat is "$#,##0.0000", "CHF#,##0.0000", etc.

I would like to keep the currency information, however, I want to have only
2 digits (i.e., 0.00).

Format(NumberFormat), "0.00") does not work.

Any idea how I can cut off the last two digits without loosing the currency
information?

Thank you for your help.

How to change parts of Cells().NumberFormat
 

Assuming that your actual number format is

"CHF "#,##0.0000

and NOT this, which won't work:

"CHF #,##0.0000"

Dim myNF As String
myNF = ActiveCell.NumberFormat
ActiveCell.NumberFormat = Left(myNF, Len(myNF) - 2)

HTH,
Bernie
MS Excel MVP


"FSPH" wrote in message
...
Hello there,

I assign Cells().NumberFormat to deal with various currencies.

Initially NumberFormat is "$#,##0.0000", "CHF#,##0.0000", etc.

I would like to keep the currency information, however, I want to have only
2 digits (i.e., 0.00).

Format(NumberFormat), "0.00") does not work.

Any idea how I can cut off the last two digits without loosing the currency
information?

Thank you for your help.

How to change parts of Cells().NumberFormat
 

Format(NumberFormat), "0.00") does not work.

Perhaps this will work...

C.NumberFormat = Replace(C.NumberFormat, ".0000", ".00")

where C is understood to be the cell (as a Range object) whose NumberFormat
you are changing. This code will not change any NumberFormats that do not
have ".0000" (without the quotes) in them; so it is safe to use on all cells
except those with ".0000" followed by additional characters as would be
found in NumberFormats with more than 4 zero-filled decimal places or,
perhaps, within text constants concatenated onto the NumberFormat.

--
Rick (MVP - Excel)


"FSPH" wrote in message
...
Hello there,

I assign Cells().NumberFormat to deal with various currencies.

Initially NumberFormat is "$#,##0.0000", "CHF#,##0.0000", etc.

I would like to keep the currency information, however, I want to have
only
2 digits (i.e., 0.00).

Format(NumberFormat), "0.00") does not work.

Any idea how I can cut off the last two digits without loosing the
currency
information?

Thank you for your help.

How to change parts of Cells().NumberFormat
 

Hello Bernie,

thanks for your input. I just realized that my problem is a bit more
complicated as some of my numbers have 4 zeros behind the dot, others only 2;
so I only want to cut off the 2 digits if the previous NumberFormat has 4
digits.

If NumberFormat with 4 digits Then Use_Bernie's_Approach.

However, I don't know how to figure out if NumberFormat has 2 or 4 digits.
Would you have any idea how to do that?

Thank you


"Bernie Deitrick" wrote:

Assuming that your actual number format is

"CHF "#,##0.0000

and NOT this, which won't work:

"CHF #,##0.0000"

Dim myNF As String
myNF = ActiveCell.NumberFormat
ActiveCell.NumberFormat = Left(myNF, Len(myNF) - 2)

HTH,
Bernie
MS Excel MVP


"FSPH" wrote in message
...
Hello there,

I assign Cells().NumberFormat to deal with various currencies.

Initially NumberFormat is "$#,##0.0000", "CHF#,##0.0000", etc.

I would like to keep the currency information, however, I want to have only
2 digits (i.e., 0.00).

Format(NumberFormat), "0.00") does not work.

Any idea how I can cut off the last two digits without loosing the currency
information?

Thank you for your help.

How to change parts of Cells().NumberFormat
 

myNF = ActiveCell.NumberFormat

If Right(myNF,4)="0000" Then
ActiveCell.NumberFormat = Left(myNF, Len(myNF) - 2)
End if

--
HTH,
Bernie
MS Excel MVP


"FSPH" wrote in message
...
Hello Bernie,

thanks for your input. I just realized that my problem is a bit more
complicated as some of my numbers have 4 zeros behind the dot, others only 2;
so I only want to cut off the 2 digits if the previous NumberFormat has 4
digits.

If NumberFormat with 4 digits Then Use_Bernie's_Approach.

However, I don't know how to figure out if NumberFormat has 2 or 4 digits.
Would you have any idea how to do that?

Thank you


"Bernie Deitrick" wrote:

Assuming that your actual number format is

"CHF "#,##0.0000

and NOT this, which won't work:

"CHF #,##0.0000"

Dim myNF As String
myNF = ActiveCell.NumberFormat
ActiveCell.NumberFormat = Left(myNF, Len(myNF) - 2)

HTH,
Bernie
MS Excel MVP


"FSPH" wrote in message
...
Hello there,

I assign Cells().NumberFormat to deal with various currencies.

Initially NumberFormat is "$#,##0.0000", "CHF#,##0.0000", etc.

I would like to keep the currency information, however, I want to have only
2 digits (i.e., 0.00).

Format(NumberFormat), "0.00") does not work.

Any idea how I can cut off the last two digits without loosing the currency
information?

Thank you for your help.