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.

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.

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.

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.

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.

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.