comma sepration in amount like Nepali Rupees

nareshjoshy

New Member
Joined
Mar 6, 2019
Messages
23
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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,900
you can try this function...

usage:
=FmtNepal(B3)

Code:
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
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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

New Member
Joined
Mar 6, 2019
Messages
23
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

Well-known Member
Joined
May 18, 2009
Messages
1,677

ADVERTISEMENT

Hi,

I took Eric link reference and converted it to UDF.

Code:
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

New Member
Joined
Mar 6, 2019
Messages
23
Thank You biz for reply. But while using your function result display as "False" and "True" rather then required.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219

ADVERTISEMENT

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:

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

Well-known Member
Joined
May 18, 2009
Messages
1,677
Hi,
Try UDF below

Code:
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

New Member
Joined
Mar 6, 2019
Messages
23
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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:

=J1<1000000000#\,##\,##\,##0.00
=J1<10000000#\,##\,##0.00
=J1<100000##,##0.00

<tbody>
</tbody>

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

Watch MrExcel Video

Forum statistics

Threads
1,109,044
Messages
5,526,439
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top