How do I limit number of decimal places after concatenation?

[mmcg_33]

Hi There

I'm trying to add a currency sign to the front of the result of a formula.

The currency sign is in a cell that can be changed by the user (in this case the cell is C19.

I'm concatenating this currency sign to the formula as follows:

=C19&+C63*$D$25

...trouble is, I get 12 decimal places (despite the fact the cell is supposed to be limited to 2!)

Anyone know how to get round this?

Thanks
Mark

 

[hatman]

one option might be =C19&text(C63*$D$25,"#.00")

 

[mmcg_33]

hey hatman

That worked, thanks!

Mark

 

[mmcg_33]

actually, just realised, this resultant figure is now treated as text...

Any way of getting this to be a number so it can be used in subsequent calculations?

 

[hatman]

Not direcly, not using a Concatenation method. You do, in fact, have several options.

1) Keep the value as a value in a hidden cell, or a on a hidden sheet:

Book1
	B	C	D	E
3	$300.06	300.0585
Sheet1

2) convert the result to a value each time you want to perform a calculation:

Book1
	B	C	D	E
5	$300.06	3000.6
Sheet1

It depends on whether you want to use all decimal places for subsequent calculations or not.

 

[mmcg_33]

splendid sir, thank you for your time and help...!

 

[pgc01]

Hi Mark, hatman

Another option is to use the worksheet change event and change the format of the cell dinamically.

With this method the cell is always a number, only the format changes.

This is a very simple example of how you would implement it in cell A1, using as in your post the simbol you write in C19.

Paste this code in the worksheet module (right-click on the worksheet's tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$19" Then
    Range("A1").NumberFormat = Chr(34) & Range("C19") & Chr(34) & "0.00"
End If
End Sub

To test is just write a number in A1 and then try different currency symbols in C19. Each time you change C19, the format in A1 reflects that change.

I've chosen the format with 2 decimals ("0.00") but you can change it to whatever in convenient to you.

Hope this helps
PGC

 

[mmcg_33]

Hi PGC

Thanks for this...

I'm not really much of a programmer, have tried to adapt the code to fit my requirements, but can't get it working...

I'm trying to do the following:

1. Use cell C20 to hold the updateable currency sign
2. Apply this to the front of all the figures in cells C54:O54 and C65:O65 and C79:O79 and N68

Could you help me with this?

Thanks
Mark

 

[pgc01]

Hi again

This code will apply the format to the cells C54:O54 and C65:O65 and C79:O79 and N68, each time you change C20.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sNumberFormat As String

If Target.Address = "$C$20" Then
    
    sNumberFormat = Chr(34) & Range("C20") & Chr(34) & "0.00"
    Range("C54:O54").NumberFormat = sNumberFormat
    Range("C65:O65").NumberFormat = sNumberFormat
    Range("C79:O79").NumberFormat = sNumberFormat
    Range("N68").NumberFormat = sNumberFormat
End If
End Sub

I use a string to store the format. This way, if you want to change the format, you just have to do it in one only place.

Remark: since this is a worksheet event, the code it will only work if it is pasted into the worksheet's module (right-click on the worksheet's tab and choose View Code).

Hope this works as you need.
PGC