求助：如何更好地实现大小写金额的转换？

azhxy · 发表于 2004-2-3 09:41

<SPAN style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-bidi-font-size: 10.0pt; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-family: 'Times New Roman'; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA">在<SPAN lang=EN-US style="FONT-SIZE: 10.5pt; FONT-FAMILY: 'Times New Roman'; mso-bidi-font-size: 10.0pt; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA; mso-fareast-font-family: 宋体">excel<SPAN style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-bidi-font-size: 10.0pt; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-family: 'Times New Roman'; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA">单元格中输入小写金额，通过“设置单元格格式”中的“中文大写数字”只能简单地实现数字大写的转换，即输入“<SPAN lang=EN-US style="FONT-SIZE: 10.5pt; FONT-FAMILY: 'Times New Roman'; mso-bidi-font-size: 10.0pt; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA; mso-fareast-font-family: 宋体">23.5<SPAN style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-bidi-font-size: 10.0pt; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-family: 'Times New Roman'; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA">”只能转成“贰拾叁<SPAN lang=EN-US style="FONT-SIZE: 10.5pt; FONT-FAMILY: 'Times New Roman'; mso-bidi-font-size: 10.0pt; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA; mso-fareast-font-family: 宋体">.<SPAN style="FONT-SIZE: 10.5pt; FONT-FAMILY: 宋体; mso-bidi-font-size: 10.0pt; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-family: 'Times New Roman'; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA">伍”，能否转换成财务中所需要的“贰拾叁元伍角”呢？望高手们指教。

nzlwc_fsb · 发表于 2004-2-3 12:54

静女!看一下前面几页,有许多实用例子啊.

azhxy · 发表于 2004-2-3 14:53

在哪里？能烦劳楼上的朋友将网址复制给我吗？

azhxy · 发表于 2004-2-3 15:42

我找到了!dispbbs.asp?boardID=5&ID=30212&page=6

ttt · 发表于 2004-3-18 21:57

我找不到

<A href="http://www.2525.cn.gs/dx.rar" target="_blank" >http://www.2525.cn.gs/dx.rar</A>

用这个工具可行，但我不知道源代码

是加载宏，你需通过点击工具－加载项－浏览来加载本工具！

可以使用＝D（）函数实现转换，如＝D（13.03），结果是“拾叁元零叁分”

[此贴子已经被作者于2004-3-18 21:58:31编辑过]

csjcsj · 发表于 2004-3-19 09:16

不要使用，此加载宏有BUG。

nzlwc_fsb · 发表于 2004-3-19 18:33

leXvEtNe.rar (9.9 KB, 下载次数: 185)

长安布衣 · 发表于 2004-3-19 20:03

我做的函数,我一直在用

不受直接使用EXCEL中IF函数的嵌套数限制,数值最大可到千亿位

EXCEL单元格只显示公式=SAY(),不显示庞大的IF公式

Function SAY(number) 'Function say(),progremmed by 长安布衣,2002-2-7 'Amended 2002-3-29 15:39 'At Changan 'email:mrttl@hotmail.com

num_int = number * 100 str_0 = "零壹贰叁肆伍陆柒捌玖" str_1 = "仟佰拾亿仟佰拾万仟佰拾元角分" num_lth = Len(Str(num_int)) - 1 str_say0 = "" For i = 1 To num_lth str_chr = Mid$(Str$(num_int), i + 1, 1) num_chr = Val(str_chr) str_s1 = Mid$(str_0, num_chr + 1, 1) str_s2 = Mid$(str_1, 14 - (num_lth - i), 1) str_say1 = str_say0 + str_s1 + str_s2 str_say0 = str_say1 Next SAY = str_say1 If number < 0 Then SAY = "(负数)" + SAY End If ' remove the "仟" when it after the "零" zeronum = InStr(1, SAY, "零仟") len_say = Len(SAY) Do While zeronum > 0 SAY = Left(SAY, zeronum - 1) + "零" + Right(SAY, len_say - zeronum - 1) zeronum = InStr(1, SAY, "零仟") len_say = Len(SAY) Loop ' remove the "佰" when it after the "零" zeronum = InStr(1, SAY, "零佰") len_say = Len(SAY) Do While zeronum > 0 SAY = Left(SAY, zeronum - 1) + "零" + Right(SAY, len_say - zeronum - 1) zeronum = InStr(1, SAY, "零佰") len_say = Len(SAY) Loop ' remove the "拾" when it after the "零" zeronum = InStr(1, SAY, "零拾") len_say = Len(SAY) Do While zeronum > 0 SAY = Left(SAY, zeronum - 1) + "零" + Right(SAY, len_say - zeronum - 1) zeronum = InStr(1, SAY, "零拾") len_say = Len(SAY) Loop ' remove the "角" when it after the "零" zeronum = InStr(1, SAY, "零角") len_say = Len(SAY) Do While zeronum > 0 SAY = Left(SAY, zeronum - 1) + "零" + Right(SAY, len_say - zeronum - 1) zeronum = InStr(1, SAY, "零角") len_say = Len(SAY) Loop ' remove the "分" when it after the "零" zeronum = InStr(1, SAY, "零分") len_say = Len(SAY) Do While zeronum > 0 SAY = Left(SAY, zeronum - 1) + "零" + Right(SAY, len_say - zeronum - 1) zeronum = InStr(1, SAY, "零分") len_say = Len(SAY) Loop ' remove the "零亿" with "亿零" zeronum = InStr(1, SAY, "零亿") If zeronum > 0 Then SAY = Left(SAY, zeronum - 1) + "亿零" + Right(SAY, len_say - zeronum - 1) End If ' remove the "零万" with "万零" zeronum = InStr(1, SAY, "零万") len_say = Len(SAY) Do While zeronum > 0 SAY = Left(SAY, zeronum - 1) + "万零" + Right(SAY, len_say - zeronum - 1) zeronum = InStr(1, SAY, "零万") len_say = Len(SAY) Loop ' remove the "零元" with "元零" zeronum = InStr(1, SAY, "零元") len_say = Len(SAY) If zeronum > 0 Then SAY = Left(SAY, zeronum - 1) + "元零" + Right(SAY, len_say - zeronum - 1) End If ' remove the "零零" with "零" zeronum = InStr(1, SAY, "零零") len_say = Len(SAY) Do While zeronum > 0 SAY = Left(SAY, zeronum - 1) + "零" + Right(SAY, len_say - zeronum - 1) zeronum = InStr(1, SAY, "零零") len_say = Len(SAY) Loop ' remove the "零亿" with "亿" zeronum = InStr(1, SAY, "零亿") len_say = Len(SAY) If zeronum > 0 Then SAY = Left(SAY, zeronum - 1) + "亿" + Right(SAY, len_say - zeronum - 1) End If ' remove the "零元" with "元" zeronum = InStr(1, SAY, "零元") len_say = Len(SAY) If zeronum > 0 Then SAY = Left(SAY, zeronum - 1) + "元" + Right(SAY, len_say - zeronum - 1) End If ' remove the "万" after "亿" zeronum = InStr(1, SAY, "亿万") len_say = Len(SAY) If zeronum > 0 Then SAY = Left(SAY, zeronum - 1) + "亿" + Right(SAY, len_say - zeronum - 1) End If ' remove "零" with "整" at the end of SAY() zeronum = Right(SAY, 1) len_say = Len(SAY) If zeronum = "零" Then SAY = Left(SAY, len_say - 1) + "整" End If If SAY = "整" Then SAY = "零元整" End If End Function

[此贴子已经被作者于2004-3-20 12:48:37编辑过]

nzlwc_fsb · 发表于 2004-3-21 07:30

9楼的朋友，你的函楼有BUG，输入100.236有问题啊，还是8楼的实用。

czwr2000 · 发表于 2004-3-22 13:12

8楼的朋友,你的我不会用，帮帮忙

		自动登录	找回密码
密码			免费注册

[求助] 求助：如何更好地实现大小写金额的转换？

看这个,很实用的,爽!