100 Seprator formula in excel

[linga]

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

 

[GraH - Guido]

Hi, maybe this works =TEXT(K7/100,"#,##")+0 or =TEXT(ROUND(K7/100,2),"#,##")+0

 

[linga]

Sir,

Not working applied both formulas , the outpt is 7 only i need as 7,14

714	7​
714	7​

 

[AlanSidman]

Maybe =ROUND(K7/100,2)

 

[shrivallabha]

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)

 

[vletm]

linga
for cell P7 =SUBSTITUTE(ROUND(K7/100,2),".",",")
and copy down.
after that use as You need.

 

[GraH - Guido]

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:


So that "." is a comma with my settings, hence I went with the suggested formula. Puzzling why it does not work with other settings.

 

[vletm]

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.