comma sepration in amount like Nepali Rupees

[nareshjoshy]

Dear Sir,

I want to insert comma (,) in the number like as standrad format of amount. In standrad format 1 million write as 1,000,000.00 but I want to write 10,00,000.00 ( this comma sepration is done in Nepali Amount). First Comma is inserted after 3 digit like as Standrad Format (English Amount) and then after every 2 digit comma should be inserted. after dot digit treated as standrad format.

Can it possibe to make function in excel by VBA.

 

[ranman256]

you can try this function...

usage:
=FmtNepal(B3)

Function FmtNepal(ByVal pvVal)
Dim i
Dim vNum, vOut

on error resume next
vNum = pvVal
i = InStrRev(vNum, ".")
vOut = Mid(vNum, i)

If i > 0 Then vNum = Left(vNum, i - 1)
vPart = Right(vNum, 3)
vNum = Left(vNum, Len(vNum) - 3)
vOut = vPart & vOut

While Len(vNum) > 2
   vPart = Right(vNum, 2)
   vNum = Left(vNum, Len(vNum) - 2)
   vOut = vPart & "," & vOut
Wend
If vNum = "" Then
   FmtNepal = vOut
Else
   FmtNepal = vNum & "," & vOut
End If
End Function

 

[Eric W]

This link offers a macro that applies a custom format to the cell that will display the number as desired, and leaves it a number. Due to the way that custom formats work, you'll need a different format for different number ranges.

http://citalhack.blogspot.com/2013/0...yle-comma.html

It's also worth noting that this technique can be used without VBA. You can set up a series of Conditional Formatting rules that looks at the value of the cell, and applies the right one. There will be 1 rule for each case in the macro.

 

[nareshjoshy]

Thank You ranman256 and eric w for reply.

I prefer function

=FmtNepal(B3)

of ranman256 and it is working well but while inserting upto 2 digit it return error. i.e when I enter 1 then result came 1,1 and when I enter 12 it returen 12,12. So how can it fixed. result should be 1 and 12 respectively.

Dear eric w, your macro code is working well but it has to run each time. Is there any way to run this macro automatic after updating certain cell?


thank you.

 

[Biz]

Hi,

I took Eric link reference and converted it to UDF.

Function FmtNepal2(c As Range)

Application.Volatile (True)
On Error Resume Next
Select Case Abs(c.Value)
Case Is < 100000
FmtNepal2 = c.Cells.NumberFormat = "##,##0.00"
Case Is < 10000000
FmtNepal2 = c.Cells.NumberFormat = "#\,##\,##0.00"
Case Is < 1000000000
FmtNepal2 = c.Cells.NumberFormat = "#\,##\,##\,##0.00"
Case Is < 1000000000
FmtNepal2 = c.Cells.NumberFormat = "#\,##\,##\,##0.00"
Case Is < 100000000000#
FmtNepal2 = c.Cells.NumberFormat = "#\,##\,##\,##\,##0.00"
Case Else
FmtNepal2 = c.Cells.NumberFormat = "#\,##\,##\,##\,##\,##0.00"
End Select
End Function

Does it help?

Biz

 

[nareshjoshy]

Thank You biz for reply. But while using your function result display as "False" and "True" rather then required.

 

[Eric W]

nareshjoshy,

There are 2 ways you can apply the formatting automatically. Either with Conditional Formatting, or with an Worksheet_Change event. In both cases, I'd need to know the location of your Nepali amounts. Are they in a particular column, or row? What version of Excel do you have?

Here is how to use the Change event, assuming you want to format any cell in column C. Open a blank test workbook. Right click on the sheet tab on the bottom and select "View Code". In the sheet that opens, enter this code:

Private Sub Worksheet_Change(ByVal target As Range)
Dim c As Range

    On Error GoTo NoRange:
    For Each c In Intersect(target, Range("C:C"))
        Select Case Abs(c.Value)
            Case Is < 100000
                c.Cells.NumberFormat = "##,##0.00"
            Case Is < 10000000
                c.Cells.NumberFormat = "#\,##\,##0.00"
            Case Is < 1000000000
                c.Cells.NumberFormat = "#\,##\,##\,##0.00"
            Case Is < 1000000000
                c.Cells.NumberFormat = "#\,##\,##\,##0.00"
            Case Is < 100000000000#
                c.Cells.NumberFormat = "#\,##\,##\,##\,##0.00"
            Case Else
                c.Cells.NumberFormat = "#\,##\,##\,##\,##\,##0.00"
        End Select
    Next c
NoRange:
    
End Sub

Press Alt-Q to exit the editor. Now try typing various amounts in column C to see how it works.

If you don't want a macro enabled workbook, you can use Conditional Formatting, although it takes a bit more to set up. Let us know if the macro works for you, or if you need help adapting it to your range, or if you want to try the Conditional Formatting.

 

[Biz]

Hi,
Try UDF below

Function FmtNepal2(c As Range) As Variant
Dim var
Application.Volatile (True)
On Error Resume Next
Select Case Abs(c.Value)
Case Is < 100000
FmtNepal2 = Format(c.Value, "##,##0.00")
Case Is < 10000000
FmtNepal2 = Format(c.Value, "#\,##\,##0.00")
Case Is < 1000000000
FmtNepal2 = Format(c.Value, "#\,##\,##\,##0.00")
Case Is < 1000000000
FmtNepal2 = Format(c.Value, "#\,##\,##\,##0.00")
Case Is < 100000000000#
FmtNepal2 = Format(c.Value, "#\,##\,##\,##\,##0.00")
Case Else
FmtNepal2 = Format(c.Value, "#\,##\,##\,##\,##\,##0.00")
End Select
End Function

 

[nareshjoshy]

Thank You Eric W. and thank you biz. Both code is working well.

Dear Eric w., My I'm using Office 2007 and My amount stored in "J:J" column. I used your macro code and it work good.
I would pleased to Know about conditional formation to solve this problem.


Dear Biz, I used your function and It is very helpful for me. Thank you for your function.

 

[Eric W]

I just noticed that the original macro from the link I posted has 2 lines of code duplicated, which I mechanically just copied, and Biz has them too. It doesn't hurt, just makes the routines longer.

In any case, to use Conditional Formatting, open a new workbook. We don't want the existing macros to interfere. Select column J and apply this custom format:

#\,##\,##\,##\,##\,##0.00

You can right click on the J on the top of the column, select "Format Cells", click the Number tab, then click Custom, and paste that format into the box under Type:.

Once that's done, select column J again, click Conditional Formatting > New rule > Use a formula > and enter this formula:

=J1<100000000000

Then click Format... > Number > Custom > and paste this format:


#\,##\,##\,##\,##0.00

Click OK. Repeat 3 more times using these formulas and formats, in this order:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]=J1<1000000000[/TD]
[TD]#\,##\,##\,##0.00[/TD]
[/TR]
[TR]
[TD]=J1<10000000[/TD]
[TD]#\,##\,##0.00[/TD]
[/TR]
[TR]
[TD]=J1<100000[/TD]
[TD]##,##0.00[/TD]
[/TR]
</tbody>[/TABLE]

Now the formats should all be applied automatically as you enter number in the column.