## Monday, March 8, 2010

### Format Numbers : Excel Currency Format

You can add currency symbol to your numbers in excel.

Open Number Format dialog box using Format Cells option or just press Ctrl+1

1. Click Accounting
2. Choose currency symbol from drop down

In case you do not get your currency symbol from drop down, add your symbol using custom formating option in the same dialogbox
1. Click Custom
2. Type - "Rs "#,##0_);[Red]("Rs "#,##0)

I use "Rs " to show numbers in Indian Currency. You can replace it with your symblol.

1. If I am not mistaken the Rs. symbol will be there as per International settings in Control Panel.

Also http://www.dq.winsila.com/tips-tricks/an-excel-addin-to-show-currency-in-indian-format-and-words.html is a great resource to have.

Regards,

PS.: BTW, your posts at PHD are welcomed.

2. Hi,

How can I sum the numbers in a column in Excel where some numbers carry a "<" sign in front of them (such as <1) and I want to include numbers such "<1" in calculation as "1".

For Example if numbers are entered as follows

0.5
<1
0.5

I would like to value to be returned as "2" when I apply "sum" function.

Thanks

3. Hi Vijay

Excel does not recoganise numbers with "<" sign as numbers and will not include them in calculation. You will need to remove them before getting them included into calculation.

Some simple steps to remove them quicly are :-

1. Select the cells or entire range with numbers having "<" before them.
2. Press Ctrl+H
3. write < in find value
4. Leave replace value as blank and press replace all button

You will have this removed from all the numbers and excel will recoginse them as numbers that can be used by formulas.

I hope this helps, let me know in case any further help

Regards//Yogesh Gupta

4. @Vijay, Yogesh,

You can use this formula to perform the summation (just change the range to encompass your cells...

=SUMPRODUCT(1*SUBSTITUTE(A1:A1000,"<",""))

5. Thanks Rick - I would have never found it.

Thanks Yogesh for a helpful site.

6. @Yogesh,
Pl let me know how can i get the accounting type display format through programming in C#.Net.
I using your Yogesh.ExcelXml so pl let me know according this asap.

Thanks
Anil

7. DEAR YOGESH JI
WISHING YOU HAPPY NEW YEAR

Regards

Harish Nebhwani , Sultanate of Oman

8. I WANT TO USE DOLLARS ( VALUE) instead of Rupees ( VALUE )

Also Need password for your Module to edit.

9. Sir,
How to Convert number into gujarati language ,
So please give me function to conver into gujarati words. please give module.

10. Dear all,
I want to subtract from 5/13/2016 1:20:39 AM to 5/13/2016 12:00:00 AM and result is in min

11. Help needed...

I exported a file to Excel. I know how to format the cells and change the currency. However, I can't seem to format the cells to remove nor change the "PhP" sign on all the numbers I have here:

PhP1,295,679.22
PhP1,521,356.25
PhP79,200.00
PhP2,594,885.60
PhP124,637.25
PhP39,000.00
PhP163,800.00
PhP9,000.00

All I want is to remove the "PhP" sign. Please help!

12. Help needed...

I exported a file to Excel. I know how to format the cells and change the currency. However, I can't seem to format the cells to remove nor change the "PhP" sign on all the numbers I have here:

PhP1,295,679.22
PhP1,521,356.25
PhP79,200.00
PhP2,594,885.60
PhP124,637.25
PhP39,000.00
PhP163,800.00
PhP9,000.00

All I want is to remove the "PhP" sign. Please help!

1. Use substitute formula
=Substitute (cell,"PhP","")

13. I want to remove \$ value & keep the number value intact How to do

14. thanks for the tips and information.currency converter i really appreciate it..

15. Shalom, Om swastiastu, Namo buddhaya, Salam kebajikan, pada artikel kali ini kami akan memberikan kepada kamu Panduan Cara Bet 2D Togel Klik4D Online yang tersedia di S128cash. Berhubung permainan togel adalah game legendaris di Indonesia, tahukah kamu kalau game togel sekarang bisa dimainkan secara online ? (Baca Selengkapnya Disini...)