在Office Excel中人民币大写无元角分如何解决?

发布网友 发布时间：2022-04-24 14:12

我来回答

共3个回答

热心网友 时间：2023-10-15 20:46

　　a、单击“工具”--“宏”--“Visual Basic 编辑器”
　　b、单击“Visual Basic 编辑器”菜单栏“运行”--“运行宏”
　　c、输入宏名，如 "zh" ,单击“创建”
　　d、键入以下代码：

　　'a1为数字小写单元格，a2为中文大写单元格
　　Const strN = "零壹贰叁肆伍陆柒捌玖"
　　Const strG = "拾佰仟万亿"
　　Const intN = "01234567"

　　Dim Zero_Count As Long '读零计数

　　Private Function GetN(ByVal N As Long) As String
　　GetN = Mid(strN, N + 1, 1)
　　End Function

　　Private Function GetG(ByVal G As Long) As String
　　Select Case G
　　Case 1
　　GetG = ""
　　Case 2, 6
　　GetG = Mid(strG, 1, 1)
　　Case 3, 7
　　GetG = Mid(strG, 2, 1)
　　Case 4, 8
　　GetG = Mid(strG, 3, 1)
　　Case 5
　　GetG = Mid(strG, 4, 1)
　　Case 9
　　GetG = Mid(strG, 5, 1)
　　End Select
　　End Function

　　Private Function ReadLongNumber(ByVal LongX As String) As String
　　Dim numberx As String
　　Dim l As Long '长度
　　Dim m As Long '多余位数
　　Dim c As Long '循环次数
　　Dim i As Long, j As Long '标志
　　Dim CurN As String

　　numberx = LongX
　　l = Len(numberx)
　　Do Until l < 9
　　m = l Mod 8
　　If m = 0 Then m = 8
　　CurN = Left(numberx, m)
　　If ReadIntNumber(CurN) <> "零" Then
　　ReadLongNumber = ReadLongNumber & ReadIntNumber(CurN) & "亿"
　　Else
　　ReadLongNumber = ReadLongNumber & "亿"
　　End If
　　numberx = Right(numberx, Len(numberx) - m)
　　l = Len(numberx)
　　Loop

　　ReadLongNumber = ReadLongNumber & ReadIntNumber(numberx)

　　If Len(ReadLongNumber) > 2 And Right(ReadLongNumber, 1) = "零" Then '去尾 零
　　ReadLongNumber = Left(ReadLongNumber, Len(ReadLongNumber) - 1)

　　End If

　　If Mid(ReadLongNumber, 1, 2) = "壹拾" Then
　　'掐头 壹拾
　　ReadLongNumber = Right(ReadLongNumber, Len(ReadLongNumber) - 1)
　　Mid(ReadLongNumber, 1, 1) = "拾"
　　End If
　　Zero_Count = 0
　　End Function

　　Private Function ReadIntNumber(ByVal numberx As String) As String
　　Dim l As Long '长度
　　Dim m As Long '多余位数
　　Dim c As Long '循环次数
　　Dim i As Long, j As Long '标志
　　Dim CurN As String

　　If Val(numberx) = 0 Then ReadIntNumber = GetN(0): Exit Function
　　l = Len(numberx)
　　If l > 8 Then Exit Function
　　m = l Mod 9
　　CurN = Right(numberx, m)
　　For i = Len(CurN) To 1 Step -1
　　If GetN(Int(Mid(CurN, i, 1))) = "零" And Zero_Count = 1 Then
　　If GetG(Len(CurN) - i + 1) = "万" Then
　　If (Not (Val(Left(CurN, Len(CurN) - 5)) = 0)) Then
　　ReadIntNumber = GetG(Len(CurN) - i + 1) & ReadIntNumber
　　End If
　　End If
　　Else
　　If GetN(Int(Mid(CurN, i, 1))) = "零" Then
　　ReadIntNumber = GetN(Int(Mid(CurN, i, 1))) & ReadIntNumber
　　If GetG(Len(CurN) - i + 1) = "万" Then
　　If (Not (Val(Left(CurN, Len(CurN) - 5)) = 0)) Then ReadIntNumber = GetG(Len(CurN) - i + 1) & ReadIntNumber
　　End If
　　Zero_Count = 1
　　Else
　　ReadIntNumber = GetG(Len(CurN) - i + 1) & ReadIntNumber
　　ReadIntNumber = GetN(Int(Mid(CurN, i, 1))) & ReadIntNumber
　　Zero_Count = 0
　　End If
　　End If
　　Next i
　　'Loop
　　If Len(ReadIntNumber) > 2 And Right(ReadIntNumber, 1) = "零" Then
　　'去尾 零
　　ReadIntNumber = Left(ReadIntNumber, Len(ReadIntNumber) - 1)
　　End If
　　If Mid(ReadIntNumber, 1, 2) = "壹拾" Then
　　'掐头 壹拾
　　ReadIntNumber = Right(ReadIntNumber, Len(ReadIntNumber) - 1)
　　Mid(ReadIntNumber, 1, 1) = "拾"
　　End If
　　End Function

　　Public Function ReadNumber(ByVal numberx As String) As String
　　Dim LongX As String
　　Dim PointX As String
　　Dim LongLong As Long
　　Dim bFS As Boolean '负数

　　If Not IsNumeric(numberx) Then
　　ReadNumber = ""
　　Exit Function
　　End If

　　If CDbl(numberx) < 0 Then
　　numberx = -numberx
　　bFS = True
　　End If

　　numberx = CStr(Format(numberx, "General Number"))

　　LongLong = InStr(1, numberx, ".")

　　If LongLong <> 0 Then
　　ReadNumber = ReadLongNumber(Left(numberx, LongLong - 1))
　　ReadNumber = ReadNumber & "点" & ReadSmallNumber(Right(numberx, Len(numberx) - LongLong))
　　Else
　　ReadNumber = ReadLongNumber(numberx)
　　End If
　　If bFS = True Then
　　ReadNumber = "负" & ReadNumber
　　End If
　　End Function

　　Private Function ReadSmallNumber(SmallNumber As String) As String
　　Dim i As Long
　　For i = 1 To Len(SmallNumber)
　　ReadSmallNumber = ReadSmallNumber & GetN(Mid(SmallNumber, i, 1))
　　Next i
　　End Function

　　Private Function ReadSmallNumberToRMB(SmallNumber As String) As String

　　ReadSmallNumberToRMB = GetN(Mid(SmallNumber, 1, 1)) & "角" & GetN(Mid(SmallNumber, 2, 1)) & "分"
　　End Function

　　Public Function ReadNumberToRMB(ByVal numberx As String) As String
　　Dim LongX As String
　　Dim PointX As String
　　Dim LongLong As Long
　　Dim bFS As Boolean '负数
　　If Not IsNumeric(numberx) Then
　　ReadNumberToRMB = ""
　　Exit Function
　　End If
　　If CDbl(numberx) < 0 Then
　　numberx = -numberx
　　bFS = True
　　End If
　　numberx = CStr(Format(numberx, "#.00"))
　　LongLong = InStr(1, numberx, ".")
　　If Right(numberx, Len(numberx) - LongLong) <> "" Then
　　ReadNumberToRMB = ReadLongNumber(Left(numberx, LongLong - 1))

　　ReadNumberToRMB = ReadNumberToRMB & "元" & ReadSmallNumberToRMB(Right(numberx, Len(numberx) - LongLong))
　　Else
　　ReadNumberToRMB = ReadLongNumber(numberx)
　　End If
　　If bFS = True Then
　　ReadNumberToRMB = "负" & ReadNumberToRMB
　　End If
　　End Function

　　Sub zh()
　　Range("a2") = ReadNumberToRMB(Range("a1"))
　　End Sub

热心网友 时间：2023-10-15 20:46

用人民币的专用符号
羊头啊

热心网友 时间：2023-10-15 20:47

我还是比较同意我楼上的回答哦，我看是多可以的，可以帮你了撒，如果还不懂的话，就去问银行嘛