• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

getting comma separation

P

paramnayak

Member
dear all,
how can i get comma separation when i am using formula of this type

="diff "&N2-M2
where in N2 say I have 1,52,500
and in M2 I have 11,200.

the output should be ------------ diff 1,41,300

thanx and rgds
 
Convert the cells containing the numbers to numbers first

If the numbers in the cells are numbers and are displayed using this format the formula
="diff "&N2-M2
Should work

Other wise please attach a sample file
 
Hi Nayak ,

This is something you can figure out this way.

1. The moment you add the words diff to the output , what is going to result is a text string.

Hence formatting the cell will not give you the result you are looking for ; the only way to get the desired output format will be to use the TEXT function with the desired output format string.

If your system locale is India , then when you format a cell by clicking on the comma button , the format which results is an Accounting format , with the following format string :

_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_

It is a simple matter to eliminate the decimal places , to get :

_ * #,##0_ ;_ * -#,##0_ ;_ * "-"??_ ;_ @_

Use this as the output format string with the TEXT function , as follows :

="diff " & TEXT(N2-M2,"_ * #,##0_ ;_ * -#,##0_ ;_ * ""-""??_ ;_ @_ ")

Note that the two double quotes around the hyphen are required because the normal double quote is around the format string itself , and so any quote symbol within has to be used twice for it to be recognized as a literal double quote.

Narayan
 
Hi Nayak,

In addition to above:
I've visited Sir Hui's thread and found this indian currency format:
Link:
[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0

This can be modified with "Diff":
[>=10000000]"Diff "##\,##\,##\,##0;[>=100000] "Diff "##\,##\,##0;"Diff "##,##0

So the result will be in number with =N2-M2

Regards,
 
You must log in or register to reply here.
Back
Top