1 Excel中如何创建名为 SpellNumber 的示例函数
- 启动 Microsoft Excel。
- 按 Alt+F11 启动 Visual Basic 编辑器。
- 在“插入”菜单上，单击“模块”。
- 在模块表中键入下面的代码。
Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " ' String representation of amount. MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none. DecimalPlace = InStr(MyNumber, ".") ' Convert cents and set MyNumber to dollar amount. If DecimalPlace > 0 Then Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber <> "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Dollars Case "" Dollars = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select Select Case Cents Case "" Cents = " and No Cents" Case "One" Cents = " and One Cent" Case Else Cents = " and " & Cents & " Cents" End Select SpellNumber = Dollars & Cents End Function ' Converts a number from 100-999 into text Function GetHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right("000" & MyNumber, 3) ' Convert the hundreds place. If Mid(MyNumber, 1, 1) <> "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred " End If ' Convert the tens and ones place. If Mid(MyNumber, 2, 1) <> "0" Then Result = Result & GetTens(Mid(MyNumber, 2)) Else Result = Result & GetDigit(Mid(MyNumber, 3)) End If GetHundreds = Result End Function ' Converts a number from 10 to 99 into text. Function GetTens(TensText) Dim Result As String Result = "" ' Null out the temporary function value. If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19... Select Case Val(TensText) Case 10:Result = "Ten" Case 11:Result = "Eleven" Case 12:Result = "Twelve" Case 13:Result = "Thirteen" Case 14:Result = "Fourteen" Case 15:Result = "Fifteen" Case 16:Result = "Sixteen" Case 17:Result = "Seventeen" Case 18:Result = "Eighteen" Case 19:Result = "Nineteen" Case Else End Select Else ' If value between 20-99... Select Case Val(Left(TensText, 1)) Case 2:Result = "Twenty " Case 3:Result = "Thirty " Case 4:Result = "Forty " Case 5:Result = "Fifty " Case 6:Result = "Sixty " Case 7:Result = "Seventy " Case 8:Result = "Eighty " Case 9:Result = "Ninety " Case Else End Select Result = Result & GetDigit _ (Right(TensText, 1)) ' Retrieve ones place. End If GetTens = Result End Function ' Converts a number from 1 to 9 into text. Function GetDigit(Digit) Select Case Val(Digit) Case 1:GetDigit = "One" Case 2:GetDigit = "Two" Case 3:GetDigit = "Three" Case 4:GetDigit = "Four" Case 5:GetDigit = "Five" Case 6:GetDigit = "Six" Case 7:GetDigit = "Seven" Case 8:GetDigit = "Eight" Case 9:GetDigit = "Nine" Case Else:GetDigit = "" End Select End Function
如何使用 SpellNumber 示例函数
要使用该示例函数将某数字更改为书面文本，请使用下列示例中演示的方法之一：方法 1：直接输入
通过将下面的公式输入单元格中，可以将 32.50 更改为“Thirty Two Dollars and Fifty Cents”：方法 2：单元格引用
可以引用工作簿中的其他单元格。例如，在单元格 A1 中输入数字 32.50，然后在另一单元格中键入下面的公式：方法 3：插入函数
要使用“插入函数” ，请按照下列步骤操作：Excel 2003：
- 选择所需的单元格。
- 单击“常用”工具栏中的“插入函数”。
- 在“或选择类别”下，单击“用户定义”。
- 在“选择函数”列表中，单击“SpellNumber”，然后单击“确定”。
- 输入所需的数字或单元格引用，然后单击“确定”。
Excel 2007 和 2010：
- 选择所需的单元格。
- 单击“公式”功能区上的“插入函数”。
- 在“或选择类别”下，单击“用户定义”。
- 在“选择函数”列表中，单击“SpellNumber”，然后单击“确定”。
- 输入所需的数字或单元格引用，然后单击“确定”。
二、sqlserver函数实现
函数一
CREATE FUNCTION [dbo].[f_num_eng1] (@num numeric(15,2))
RETURNS varchar(400)
AS
BEGIN
--All rights reserved. pbsql
DECLARE @i int,@hundreds int,@tenth int,@one int
DECLARE @thousand int,@million int,@billion int
DECLARE @numbers varchar(400),@s varchar(15),@result varchar(400)
SET @numbers='one two three four five '
+'six seven eight nine ten '
+'eleven twelve thirteen fourteen fifteen '
+'sixteen seventeen eighteen nineteen '
+'twenty thirty forty fifty '
+'sixty seventy eighty ninety '
SET @s=RIGHT('000000000000000'+CAST(@num AS varchar(15)),15)
SET @billion=CAST(SUBSTRING(@s,1,3) AS int)--将12位整数分成4段：十亿、百万、千、百十个
SET @million=CAST(SUBSTRING(@s,4,3) AS int)
SET @thousand=CAST(SUBSTRING(@s,7,3) AS int)
SET @result=''
SET @i=0
WHILE @i<=3
BEGIN
SET @hundreds=CAST(SUBSTRING(@s,@i*3+1,1) AS int)--百位0-9
SET @tenth=CAST(SUBSTRING(@s,@i*3+2,1) AS int)
SET @one=(CASE @tenth WHEN 1 THEN 10 ELSE 0 END)+CAST(SUBSTRING(@s,@i*3+3,1) AS int)--个位0-19
SET @tenth=(CASE WHEN @tenth<=1 THEN 0 ELSE @tenth END)--十位0、2-9
IF (@i=1 and @billion>0 and (@million>0 or @thousand>0 or @hundreds>0)) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 or @hundreds>0)) or
(@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds>0))
SET @result=@result+' '--百位不是0则每段之间加连接符,
IF (@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds=0 and (@tenth>0 or @one>0)))
SET @result=@result+' and '--百位是0则加连接符AND
IF @hundreds>0
SET @result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+' hundred'
IF @tenth>=2 and @tenth<=9
BEGIN
IF @hundreds>0
SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@tenth*10+171,10))
END
IF @one>=1 and @one<=19
BEGIN
IF @tenth>0
SET @result=@result+' '
ELSE
IF @hundreds>0
SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@one*10-9,10))
END
IF @i=0 and @billion>0
SET @result=@result+' billion'
IF @i=1 and @million>0
SET @result=@result+' million'
IF @i=2 and @thousand>0
SET @result=@result+' thousand'
SET @i=@i+1
END
IF SUBSTRING(@s,14,2)<>'00'
BEGIN
SET @result=@result+' point '
IF SUBSTRING(@s,14,1)='0'
SET @result=@result+'zero'
ELSE
SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,1) AS int)*10-9,10))
IF SUBSTRING(@s,15,1)<>'0'
SET @result=@result+' '+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,15,1) AS int)*10-9,10))
END
RETURN(@result)
END
函数二（两个函数一起）
create FUNCTION [dbo].[ThreeDigit]
(
@integer int
)
RETURNS varchar(100)
WITH EXECUTE AS CALLER
AS
BEGIN
declare @atoe table(a int,e varchar(10))
insert into @atoe select 0,'zero' union all select 1,'one'
union all select 2,'two' union all select 3,'three'
union all select 4,'four' union all select 5,'five'
union all select 6,'six' union all select 7,'seven'
union all select 8,'eight' union all select 9,'nine'
union all select 10,'ten' union all select 11,'eleven'
union all select 12,'twelve' union all select 13,'thirteen'
union all select 14,'fourteen' union all select 15,'fifteen'
union all select 16,'sixteen' union all select 17,'seventeen'
union all select 18,'eighteen' union all select 19,'nineteen'
union all select 20,'twenty' union all select 30,'thirty'
union all select 40,'forty' union all select 50,'fifty'
union all select 60,'sixty' union all select 70,'severty'
union all select 80,'eighty' union all select 90,'ninety'
declare @english varchar(100)
set @english=''
if @integer>99
begin
select @english=e+' hundred ' from @atoe where @integer/100=a
set @integer=@integer % 100
if @integer>0
set @english=@english+'and '
end
if @integer<=20 and @integer>0
select @english=@english+e+' ' from @atoe where @integer=a
if @integer>20
begin
select @english=@english+e+' ' from @atoe where @integer/10*10=a
set @integer=@integer % 10
if @integer>0
select @english=@english+e+' ' from @atoe where @integer=a
end
RETURN @english
END
CREATE FUNCTION [dbo].[Digit2English]
(
@arabia decimal(38,17)
)
RETURNS varchar(1000)
AS
BEGIN
declare @atoe table(a int,e varchar(10))
insert into @atoe select 0,'zero' union all select 1,'one'
union all select 2,'two' union all select 3,'three'
union all select 4,'four' union all select 5,'five'
union all select 6,'six' union all select 7,'seven'
union all select 8,'eight' union all select 9,'nine'
declare @integer bigint,@trillion int,@billion int,@million int,@thousand int,@hundred int,@english varchar(1000)
select @integer=@arabia,@english=''
select @trillion=@integer % 1000000000000000/1000000000000,@billion=@integer % 1000000000000/1000000000,
@million=@integer % 1000000000/1000000,@thousand=(@integer % 1000000)/1000,@hundred=(@integer % 1000)
if @trillion>0
set @english=@english + dbo.ThreeDigit(@trillion) + 'trillion '
if @billion>0
set @english=@english + dbo.ThreeDigit(@billion) + 'billion '
if @million>0
set @english=@english + dbo.ThreeDigit(@million) + 'million '
if @thousand>0
set @english=@english + dbo.ThreeDigit(@thousand) + 'thousand '
if @hundred>0
set @english=@english + dbo.ThreeDigit(@hundred)
if @english=''
set @english='zero '
if @arabia-@integer>0.000000000
begin
declare @decimal decimal(18,17)
select @english=@english+'point ',@decimal=@arabia-@integer
while @decimal>0.0
begin
select @english=@english+e+' ' from @atoe where cast(@decimal*10 as int)=a
set @decimal=@decimal*10-cast(@decimal*10 as int)
end
end
return @english+' only'
END
