Comma Seperation issues: Numeric values are completely displayed differently

[NimishK]

Hello
OMG
What i face now is
when values displayed in formula bar as 200600 it displays as 200,600 in cell instead of 2,00,600 ie incorrect comma placement
Following values
4,14,180 to display as 4,14,180 it displays as 4104,180
3,18,600 to display as 3,18,600 it dispalys as 3108,600
17676.40 displayed as 17,676 which is correct
400000 to display as 4,00,000 it dispalys as 40,00,000
i really dont know how addtional 0 is coming

For Each nmbrFrmtCell In .Range("CI3:CI50")
   nmbrFrmtCell.NumberFormat = Trim(Replace(Format(String(Len(Int(nmbrFrmtCell.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) 
Next nmbrFrmtCell

Desired is to have correct placement of comma and exact Numeric Value
when Tens, Hundreds, Thousand, TenThousand, One Lac, Ten lacs so on

Thanks NimishK

 

[NimishK]

Sending herewith file attached

Request you to compare two columns C and D from row 176 onward till 427
D columns value are marked with yellow for you to see the difference

Pl check the yellow marked values in FormulaBar also. you will see the difference
after i executed the code in workbook_open
i;ve added " ' " for not executing the code
for you to see the difference

Let me know the correction
The Range is differing from post#1 and code in the file

https://www.dropbox.com/preview/NumberFormat3.xlsm?role=personal

 

[sandy666]

your file is NOT shared

 

[NimishK]

sorry
PFA
https://www.dropbox.com/s/hb9itjmuk6ad5zc/NumberFormat3.xlsm?dl=0

 

[sandy666]

hope I understand it correctly, it should be like in green column?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Instalment Amount 1[/td][td]

Instalment Amount 1 ​

[/td][td=bgcolor:#C6E0B4]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

88500​

[/td][td]

88,500​

[/td][td]

88,500​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

37760​

[/td][td]

37,760​

[/td][td]

37,760​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

28320​

[/td][td]

28,320​

[/td][td]

28,320​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

33040​

[/td][td]

33,040​

[/td][td]

33,040​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

843604​

[/td][td=bgcolor:#FFFF00]

8403,604​

[/td][td]

843,604​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

28320​

[/td][td]

28,320​

[/td][td]

28,320​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

35400​

[/td][td]

35,400​

[/td][td]

35,400​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

200000​

[/td][td=bgcolor:#FFFF00]

2000,000​

[/td][td]

200,000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

200000​

[/td][td=bgcolor:#FFFF00]

2000,000​

[/td][td]

200,000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

26904​

[/td][td]

26,904​

[/td][td]

26,904​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

100000​

[/td][td=bgcolor:#FFFF00]

1000,000​

[/td][td]

100,000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

70800​

[/td][td]

70,800​

[/td][td]

70,800​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

30000​

[/td][td]

30,000​

[/td][td]

30,000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

250000​

[/td][td=bgcolor:#FFFF00]

2500,000​

[/td][td]

250,000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

106200​

[/td][td=bgcolor:#FFFF00]

1006,200​

[/td][td]

106,200​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

40120​

[/td][td]

40,120​

[/td][td]

40,120​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

220188​

[/td][td=bgcolor:#FFFF00]

2200,188​

[/td][td]

220,188​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

42480​

[/td][td]

42,480​

[/td][td]

42,480​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

33040​

[/td][td]

33,040​

[/td][td]

33,040​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

70800​

[/td][td]

70,800​

[/td][td]

70,800​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

589941​

[/td][td=bgcolor:#FFFF00]

5809,941​

[/td][td]

589,941​

[/td][/tr]
[/table]

custom format: _-* #,##0_ -;-* #,##0_ -;_-* "-"_-;_-@_-
or simply : Accounting with 0 decimal places

 

[NimishK]

Yes, you have understood perfectly. it should be like Green column

i used the custom format from worksheet as per your suggestion. Nothing worked
then

With ws

[I]'For Each nmbrFrmtCell In .Range("D3:D427")
'   nmbrFrmtCell.NumberFormat = Trim(Replace(Format(String(Len(Int(nmbrFrmtCell.Value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
'Next
[/I].Columns("E").NumberFormat = "_-* #,#[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_"]#0_[/URL]  -;-* #,#[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_"]#0_[/URL]  -;_-*" & "-" & "_-;_-@_-"
End With

Still did not work. Also i closed the file and re-opened then also nothing worked.
I don't know whats happening. Using Excel2013

 

[sandy666]

Maybe try reset column format to General first then use custom format but I'm far far away from vba so I can't help

 

[NimishK]

the column was reset to General Format - Saved it Re-opened it. also incorporated the custom format and removed the VBA code
Sorry did not work.

 

[sandy666]

wait a moment! Custom format didn't work without vba???

 

[NimishK]

yes