帮帮忙，数字转换成大写金额

excel_acc

请问怎样把小写数字转换成大写啊，比如把  123.45 转换 成壹百贰拾三元四角五分，谢谢

rao71503

点击该单元格-右键-单元格格式-数字-特殊-中文大写数字
或  单元格-右键-单元格格式-数字-自定义-[DBNum2]G/通用格式

[此贴子已经被作者于2002-11-12 16:01:12编辑过]

excel_acc

谢谢，但是没有元角分的字样啊，我试过用以下方法，但也不能在原来的单元格显示，要引用另外的单元格数值。http://club.excelhome.net/dispbbs.asp?boardID=3&RootID=31455&ID=31455

井底之蛙

这是一个网友提供的，很好用的。借花献佛
假设A1中是要处理的数据。
IF(A1<0,"负","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分","整")

yuxiwap

如果C2为数字金额，那么就
=IF(TRUNC(C2)=0,"",TEXT(TRUNC(C2),"[DBNum2]G/通用格式")&"元")

rainbow

把下面的代码粘贴到VISUALBASIC 编辑器里。然后用a1=chmonmey(a2)



Attribute VB_Name = "CMoney"
Public Function CCh(N1) As String
Select Case N1
  Case 0
    CCh = "零"
  Case 1
    CCh = "壹"
  Case 2
    CCh = "贰"
  Case 3
    CCh = "叁"
  Case 4
    CCh = "肆"
  Case 5
    CCh = "伍"
  Case 6
    CCh = "陆"
  Case 7
    CCh = "柒"
  Case 8
    CCh = "捌"
  Case 9
    CCh = "玖"
End Select
End Function


'名称： ChMoney
'       得到数字 N1 的汉字大写
'       最大为 千万位
'       O 返回 ""
Public Function ChMoney(N1) As String
Dim tMoney As String
Dim lMoney As String
Dim tn '小数位置
Dim s1 As String '临时STRING 小数部分
Dim s2 As String '1000 以内
Dim s3 As String '10000

If N1 = "" Then
ChMoney = ""
Exit Function
End If
If N1 = 0 Then
  ChMoney = " "
  Exit Function
End If
If N1 < 0 Then
  ChMoney = "负" + ChMoney(Abs(N1))
  Exit Function
End If
tMoney = Trim(Str(N1))
tn = InStr(tMoney, ".")  '小数位置
s1 = ""

If tn <> 0 Then
  st1 = Right(tMoney, Len(tMoney) - tn)
  If st1 <> "" Then
    t1 = Left(st1, 1)
    st1 = Right(st1, Len(st1) - 1)
    If t1 <> "0" And st1 = "" Then
      s1 = s1 + CCh(Val(t1)) + "角整"
      Else
       s1 = s1 + CCh(Val(t1)) + "角"
    End If
    If st1 <> "" Then
     t1 = Left(st1, 1)
     s1 = s1 + CCh(Val(t1)) + "分"
     End If
  End If
  st1 = Left(tMoney, tn - 1)
Else
  st1 = tMoney
  s1 = "整"
End If


s2 = ""
If st1 <> "" Then
  t1 = Right(st1, 1)
  st1 = Left(st1, Len(st1) - 1)
  s2 = CCh(Val(t1)) + s2
End If

If st1 <> "" Then
  t1 = Right(st1, 1)
  st1 = Left(st1, Len(st1) - 1)
  If t1 <> "0" Then
    s2 = CCh(Val(t1)) + "拾" + s2
  Else
    If Left(s2, 1) <> "零" Then s2 = "零" + s2
  End If
End If

If st1 <> "" Then
  t1 = Right(st1, 1)
  st1 = Left(st1, Len(st1) - 1)
  If t1 <> "0" Then
    s2 = CCh(Val(t1)) + "佰" + s2
  Else
    If Left(s2, 1) <> "零" Then s2 = "零" + s2
  End If
End If

If st1 <> "" Then
  t1 = Right(st1, 1)
  st1 = Left(st1, Len(st1) - 1)
  If t1 <> "0" Then
  s2 = CCh(Val(t1)) + "仟" + s2
  Else
    If Left(s2, 1) <> "零" Then s2 = "零" + s2
  End If
End If

s3 = ""
If st1 <> "" Then
  t1 = Right(st1, 1)
  st1 = Left(st1, Len(st1) - 1)
  s3 = CCh(Val(t1)) + s3
End If


If st1 <> "" Then
  t1 = Right(st1, 1)
  st1 = Left(st1, Len(st1) - 1)
  If t1 <> "0" Then
  s3 = CCh(Val(t1)) + "拾" + s3
  Else
    If Left(s3, 1) <> "零" Then s3 = "零" + s3
  End If
End If

If st1 <> "" Then
  t1 = Right(st1, 1)
  st1 = Left(st1, Len(st1) - 1)
  If t1 <> "0" Then
  s3 = CCh(Val(t1)) + "佰" + s3
  Else
   If Left(s3, 1) <> "零" Then s3 = "零" + s3
  End If
End If

If st1 <> "" Then
  t1 = Right(st1, 1)
  st1 = Left(st1, Len(st1) - 1)
  If t1 <> "0" Then
  s3 = CCh(Val(t1)) + "仟" + s3
  End If
End If
If Right(s2, 1) = "零" Then s2 = Left(s2, Len(s2) - 1)
If Len(s3) > 0 Then
  If Right(s3, 1) = "零" Then s3 = Left(s3, Len(s3) - 1)
  s3 = s3 & "万"
End If

ChMoney = IIf(s3 & s2 = "", s1, s3 & s2 & "元" & s1)

End Function

fjjack

找个扩展函数吧,可以直接将小写转换为大写金额

小陈

本网站不是就有这个扩展函数,可以下载下来玩玩

fangnaidong

=IF(ROUND(A1,2)=0,"",IF(ROUND(ABS(A1),2)>=1,TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元","")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,"整",IF(RIGHT(TEXT(A1,".00"),4)*1>=1,IF(RIGHT(TEXT(A1,".00"),2)*1>9,"","零"),IF(ROUND(ABS(A1),2)>=1,"零",""))&IF(RIGHT(TEXT(A1,".00"),2)*1>9,TEXT(LEFT(RIGHT(TEXT(A1,".00"),2)),"[DBNum2]")&"角","")&IF(RIGHT(TEXT(A1,".00"))*1>0,TEXT(RIGHT(TEXT(A1,".00")),"[DBNum2]")&"分","整")))

edqi

确实不错，非常感谢！