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

100 Seprator formula in excel

linga

New Member
Sir,

data is in value in lakhs.

The data in cell K7 is 714.66 the value needs to be rounded and then it needs to be shown as 7,15
Like that all cells needs to be shown with comma separator.

Please provide formula or any suggestion.

File attached for reference.


Regards,

U.Linganathan
 

Attachments

  • Sales-12-JUL-2020.xlsx
    13.8 KB · Views: 9
If it is only representation you care about then you can try:

=LEFT(TEXT(ROUND(K7,0)*10,"#,##,#"),LEN(TEXT(ROUND(K7,0)*10,"#,##,#0"))-1)
 
linga
for cell P7 =SUBSTITUTE(ROUND(K7/100,2),".",",")
and copy down.
after that use as You need.
That is just cunning, @vletm .

Strangely enough when I opened the workbook I got this:
69915

So that "." is a comma with my settings, hence I went with the suggested formula. Puzzling why it does not work with other settings.
 
GraH - Guido
If someone asks 'decimal separator' 'need to be shown' other way eg from point (.) to comma (,)
without any clue - what some would have plan to do after that - then ...hmm?
... then those are not numbers anymore, but 'need to be shown' has done.
 
Back
Top