How do I limit number of decimal places after concatenation?

mmcg_33

New Member
Joined
Feb 22, 2006
Messages
7
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 :eek: (despite the fact the cell is supposed to be limited to 2!)

Anyone know how to get round this?

Thanks
Mark
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mmcg_33

New Member
Joined
Feb 22, 2006
Messages
7
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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
BCDE
3$300.06300.0585
Sheet1


2) convert the result to a value each time you want to perform a calculation:
Book1
BCDE
5$300.063000.6
Sheet1


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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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):

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

New Member
Joined
Feb 22, 2006
Messages
7
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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.

Code:
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
 

Forum statistics

Threads
1,136,309
Messages
5,674,999
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top