将三位数字(如035)转换为文本(如零三五)

Question

输入：7
输出：零-零-七，而不是七

输入：17
输出：零一七，而不是十七

输入：100
输出：一个零-零。不是一百

我希望能够像上面的例子一样进行转换，但我无法为所需的输出编写宏。我正在使用Excel 2007。

Answer 1

如果你想要纯VBA/没有公式、查找表或辅助列等，这就可以了。基本上，使用一个函数复制查找表，该函数将特定的整数值转换为所需的等效字符串。

Sub Test2()
    Dim rng As Range
    Dim cl As Range
    Dim clVal As String
    Dim strVal As String
    Dim i As Integer
    Set rng = Range("F1:F4")  '# Modify this line to be your range
    'Make sure they're all three digits:
    For Each cl In rng.Cells
        clVal = cl.Value
        Do While Not Len(clVal) >= 3
            clVal = "0" & clVal
        Loop
        'Now, parse the digits:
        For i = 1 To Len(clVal)
            strVal = strVal & " " & ConvertNumber(Mid(clVal, i, 1))
        Next
        strVal = Trim(strVal)
        cl.Value = strVal
        strVal = vbNullString
    Next
End Sub
Function ConvertNumber(i As Integer) As String
    Select Case i
        Case 1
            ConvertNumber = "one"
        Case 2
            ConvertNumber = "two"
        Case 3
            ConvertNumber = "three"
        Case 4
            ConvertNumber = "four"
        Case 5
            ConvertNumber = "five"
        Case 6
            ConvertNumber = "six"
        Case 7
            ConvertNumber = "seven"
        Case 8
            ConvertNumber = "eight"
        Case 9
            ConvertNumber = "nine"
        Case 0
            ConvertNumber = "zero"
    End Select
End Function

您可以很容易地将其调整为可以直接从工作表调用的UDF。

Answer 2

如果您的数字在从第1行开始的第a列中，并且您有一个查找表（比如D1=0，E1=0，依此类推，直到D10=9和E10=9），那么以下可能适用：

Sub Macro1()
    Range("B1").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(LEN(RC[-1])=1,""zero zero ""&VLOOKUP(1*RC[-1],C[2]:C[3],2,0),IF(LEN(RC[-1])=2,""zero ""&VLOOKUP(1*LEFT(RC[-1],1),C[2]:C[3],2,0)&"" ""&VLOOKUP(1*RIGHT(RC[-1],1),C[2]:C[3],2,0),VLOOKUP(1*LEFT(RC[-1],1),C[2]:C[3],2,0)&"" ""&VLOOKUP(1*MID(RC[-1],2,1),C[2]:C[3],2,0)&"" ""&VLOOKUP(1*RIGHT(RC[-1],1),C[2]:C[3],2,0)))"
    ActiveCell.Copy
    Range("A1").Select
    Range(ActiveCell, ActiveCell.End(xlDown)).Offset(0, 1).Select
    ActiveSheet.Paste
End Sub