Free Microsoft Excel 2013 Quick Reference

Replace million-billion number format to lakhs-crores format

How do i put comma in a number in lakhs-crores format rather than in
million-billion format as lakhs-crores format is prevalent in India

--
Sumit
------------------------------------------------------------------------
Sumit's Profile: http://www.excelforum.com/member.php...o&userid=29520
View this thread: http://www.excelforum.com/showthread...hreadid=492198


Post your answer or comment

comments powered by Disqus
How do i put comma in a number in lakhs-crores format rather than in million-billion format as lakhs-crores format is prevalent in India

Hi, I needed a macro to copy a certain range from one sheet to another and since I have no VB idea, I had someone do it for me BUT then I realized it's copying the original borders (which I don't want) and realized I need to add the

	VB:
	
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
instruction to my existing macro but I get errors. Can someone help me? I guess it's easy but since I have no clue... my original macro is (work fine as it is):


	VB:
	
Range("PLAN").Copy Destination:=Sheets("BALANCE").Range("A8") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and I'd need only the Values and Number Formats to be pasted. THANKS!!!!!

Is there any way to apply number formatting to a number that is incorporated into a text string? Please see the example below:

---
Value in cell E10: $123,456.00

Formula in cell G1: ="The grand total is "&E10&" and is due by 31 October."

Output of cell G1: The grand total is 123456 and is due by 31 October.
---

I want the output to show the number formatting i.e. the dollars sign, the comma and the decimal places. It is easy enough to put the dollars sign and decimal places in as text, but what about the comma?

Is it possible to link a custom number format to a cell within a workbook?

Currently I have the number format
#,##0.0"m (forecast)";(#,##0.0)"m (forecast)";-"m (forecast)"
used extensively throughout a workbook

I want to change the text after each number to "m (actual)". I will be
changing the text on a monthly basis. Currently I have to manually change the
format each month.

Is it possible to link a custom number format to a cell within a workbook?

Currently I have the number format
#,##0.0"m (forecast)";(#,##0.0)"m (forecast)";-"m (forecast)"
used extensively throughout a workbook

I want to change the text after each number to "m (actual)". I will be
changing the text on a monthly basis. Currently I have to manually change the
format each month.

Does anyone know how I can change the default number format to show blank
spaces rather than commas between thousands. For example:

10 000 000 and (10 000 000) instead of 10,000,000 for positives and
(10,000,000) for negatives. This needs to work such that no matter how big
the number, spaces are put in and not commas.

Also, is it possible to cover circumstances where if there are decimal
points then show them as input:

ie if I type in 5.1 that it shows up as 5.1 where as an input of 5.12 shows
as 5.12 basically depending on how many decimal points are required then
show them.

For all of it, negative numbers to be shown in brackets and in black not
red.

Cheers

Mark.

All,

I have a number format that I need assistance with:

The number format I need is 1234-000 or 1234-001

The normal number format is 1234-000, very rarely does the 1234-001 be used. So I would like the number format to left fill the left section 1234 and right fill the right section 001.
Is this possible?

I'm using Excel 2007 and am using a look up formula =isna(match(cell, cell range, 0)) to see whether a number in one column occurs in another column.

I have been doing this process successfully for over a year on Excel 2003 and more recently on Excel 2007. This is until today, when it's stopped doing what I expect - could I have changed a setting?

The correct number format for this sheet should be 0000 (so now '67' will appear as '0067' or '290' will appear as '0290' etc.) So I have applied this Custom Format to my columns: '0000'
After applying the Custom Format to the spreadsheet the numbers appear correctly - 560 now appears as 0560 - BUT in the formula bar they still only appear as the original format - so 560 is STILL 560 rather than 0560 as it should be!

As a result of this, my look up is not looking for the number in the new format, it's still looking for the number in its original format - so a look up for 0560 is actually returning results for anything with 560 in it: 560, 5600, 5601, 56000, 56007 etc.
I need my look up to be looking for 0560 NOT 560.

How do I force the numbers to become 4 digits in the formula bar so that my look up for 0560 only finds a match with a cell containing 0560 and not 560, 5600, 5601, 56000, 56007 etc.

Thank you

Scratchweasel

If i enter a formula that returns a text result (eg =c2 where cell c2
contains text), then Excel defaults that cell's number format to text.
This means that next time I edit that formula, I just get the text "=c2".
To get the result I want I need to reformat that cell back to general, then
click back in the edit formula bar. This is frustrating and tedious when it
happens a lot.
I want to set all cell number formats to general unless I specifically
overide that, so that I don't have excel deciding for me that a particular
cell should be text when it should still be general.
I hope all this makes sense.

I have problem to create a search sheet.
I can have a search for Text format. But I can't use it on Number Format.
Because I use countif, and in order to make the search to look in any part of the value, I put "*"&&"*" in the front and end of the search text.

How to make the countif or search to look in any part of the number and text together?

See attachment for example

If i enter a formula that returns a text result (eg =c2 where cell c2
contains text), then Excel defaults that cell's number format to text.
This means that next time I edit that formula, I just get the text "=c2".
To get the result I want I need to reformat that cell back to general, then
click back in the edit formula bar. This is frustrating and tedious when it
happens a lot.
I want to set all cell number formats to general unless I specifically
overide that, so that I don't have excel deciding for me that a particular
cell should be text when it should still be general.
I hope all this makes sense.

Hi,
Pls help. I have ~40 non-continuous range of cells and I need the number format to be changed base on certain conditions. Anyway, I don't think XL 2000 conditional formating can change the number format. (it should for font,border,pattern only).
Any method besides use VBA to change those region's (random location) number format one by one? Thanks in advance.

Fr, CS

Hi everyone, I am really enjoying the flexibility of excel programming with macros. I'm doing a little project and am a little stumped at a small road block.

problem: I have one cell: D59 in sheet "DDE" It is number formatted. I want to apply that same number format to a range of cells (from (4,52) to (10,52)). I used the following code:

I sense that I have a data type error.  But I'm not sure how I could correct for this.  This sounds like a really silly
mistake, I hope you guys could help me out.

Thanks in Advance,
David

I would like to change the number format on my spreadsheet from Base 10 to
Base 12, eg. 12 bottles makes up 1 case. Therefore, if I were adding up three
different cells 9 bottles + 11 bottles + 6 bottles, my result should be 2
cases 2 bottles if possible 2.2 in a case column.

I have an excel spreadsheet which pulls data from .csv files that are run from my company's database every 30 minutes. Some number data is formated to show a "+" sign in front of the positive numbers by using a custom number format [+0;-0;0].

I use the following string to combine the numbers with text:

Both E5 and I5 have this custom formatting mentioned in the previous paragraph, but it is not translating to the cell where
the above code resides.  Can anyone help me?

Hello all, I have a spreadsheet that I have calculated some numbers and have the cell formatted to # ??/??. Using the MROUND function on the cell I take a number like this 0.4375 and convert it to a fraction like using =MROUND (A1, 1/16). This gives me 7/16 instead of 0.4375. I use this to get my "measurements" to look like the fractions of an inch on a tape measure.

My question is: How can I concatenate this number together with another one and have it display properly still (e.g. take cell A1 which is 7/16 and cell B1 which is 1/2 and combine them with other text to get: 7/16" to 1/2").

Thank you for your help with this.

Hi, can anyone tell me if it is possible to set the number format within a FormulaR1C1 command? I am trying to make my code a little more beautiful and avoid the use of Select as far as possible.

I'd like the number format to be NumberFormat = "###0.00" for the following calculation, but can't work out where to put it.

Thanks very much for any help.

	VB:
	
ActiveCell.Offset((i - 1), 2).Resize(1, cNum).FormulaR1C1 = _ 
"=IF(N(R[" & B & "]C),((R[" & A & "]C/R[" & B & "]C)*100),0)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I need a custom number format to do the following:
When a whole number, shows only whole number (no decimal place)
When decimal, show decimal

For example, have this data show as typed:
45 (not 45.0)
32
6.7
43.5
87

I can't figure out a format to allow this. The closest I get is:
??.?
but that gives me:
45.
32.
6.7
43.5
87.

Does anyone know a way to fix this?

Hi,

I'm very new to vba and am making some simple userforms to enter data.

My problem is that in the quantity textbox of the userform, I want to change the number format depending on the item.

For example, if the unit is kg., I want the number format to be 0.000, while if it is nos., I want it to be 0.

With thanks in advance for all answers - and no answer is too simple.

Debkumar

I am trying to work out a custom number format to display betting fractions properly - namely 3/2 as 6/4 and 2/3 as 4/6. The following works for 3/2:


	VB:
	
[=1.5]"6/4 " ;??/?? 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However I can't get anything to work for 2/3 as it is a recurring decimal. Any ideas?

This has been driving me nuts...

I simply want to conditionally format a cells value based on another cells value:

If the control cell has a value of 0 then format target cell numbers "0"
If the control cell has a value of 1 then format target cell numbers "0.00".

Conditional formatting doesn't allow the number format to be selected. Can it be done in any other way?

Hi all...

Probably a stupid question, but still...

I have a checkbox which changes formulas, and I want the number formatting to change to percentages when the box is ticked.

Any ideas?

Hi. Is there any way to get a custom number format to display a multiple of the cell value? e.g. can I have a cell have the value 0.005 and display the value 50?

Hi!

I'm trying to get an excel custom number format to show the difference in tw dates as 0 years and 0 months...for e.g.

Cell a1 = commencement date = 1/1/05

cell a2 = expiration date = 12/31/10

Then cell a3 = Lease Term = 6 years 0 months.

Would appreciate any help I can get on this issue.

K


No luck finding an answer? You could always try Google.