How do I limit number of decimal places after concatenation?

mmcg_33

New Member
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

Excel Facts

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

hatman

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

mmcg_33

New Member
hey hatman

That worked, thanks!

Mark

mmcg_33

New Member
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

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.

mmcg_33

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

pgc01

MrExcel MVP

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)

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

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

Replies
3
Views
73
Replies
3
Views
74
Replies
2
Views
130
Replies
0
Views
74
Replies
1
Views
256

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.

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

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