Excel单元格转换教程 – 转换单位、文本和数字、货币、时区等

MS Excel 微软技术高级Excel函数

在处理Excel中的数据时，有时您可能需要对单元格进行某些数据转换，例如转换单位、转换货币、转换时区等等。

转换单位

在大多数情况下，当我们需要将数字从一个计量单位转换为另一个计量单位时，我们将使用CONVERT函数。但是，对于您尝试进行的一些单位转换，CONVERT函数将无法正常工作。

步骤1

您可以使用CONVERT函数将指定范围内的英寸分别转换为英尺、厘米和毫米的对应值。

步骤2

测量单位种类繁多。要查看属于每个类别并可与CONVERT函数的“起始单位”和“目标单位”参数一起使用的单位，请分别键入“起始单位”和“目标单位”。

=CONVERT(number, from_unit, to_unit)

其中：

number − 需要转换的数值。
from_unit − 数字的初始测量单位。
to_unit − “number”将转换到的单位。

英寸到毫米转换

选择一个单元格（例如，本例中为C2），然后将下面的公式粘贴或键入到该单元格中，然后按Enter键。首先，选择包含此结果的单元格，然后使用自动填充柄向下移动它。这将显示其余结果。

=CONVERT(A2,"in","mm")

步骤3

选择一个单元格（例如，本例中为D2），然后将下面的公式粘贴或键入到该单元格中，然后按Enter键。首先，选择包含此结果的单元格，然后使用自动填充柄向下移动它。这将显示其余结果。

=CONVERT(A2,"in","cm")

步骤4

选择一个单元格（例如，本例中为E2），然后将下面的公式粘贴或键入到该单元格中，然后按Enter键。首先，选择包含此结果的单元格，然后使用自动填充柄向下移动它。这将显示其余结果。

=CONVERT(A2,"in","ft")

文本和数字

如果您想在Excel中将数字显示为英文单词，则以下用户定义函数可能对您有用。

步骤1

要启动Microsoft Visual Basic for Applications窗口，请按住Alt键的同时按F11键。或转到开发工具菜单并选择Visual Basic。

之后，它将打开Microsoft Visual Basic for Applications。然后转到插入并选择模块。

然后复制并粘贴以下VBA代码。

Function NumberstoWords(ByVal MyNumber)
  'Update by Extendoffice 20220516
   Dim xStr As String
   Dim xFNum As Integer
   Dim xStrPoint
   Dim xStrNumber
   Dim xPoint As String
   Dim xNumber As String
   Dim xP() As Variant
   Dim xDP
   Dim xCnt As Integer
   Dim xResult, xT As String
   Dim xLen As Integer
   On Error Resume Next
   xP = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")
   xNumber = Trim(Str(MyNumber))
   xDP = InStr(xNumber, ".")
   xPoint = ""
   xStrNumber = ""
   If xDP > 0 Then
      xPoint = " point "
      xStr = Mid(xNumber, xDP + 1)
      xStrPoint = Left(xStr, Len(xNumber) - xDP)
      For xFNum = 1 To Len(xStrPoint)
         xStr = Mid(xStrPoint, xFNum, 1)
         xPoint = xPoint & GetDigits(xStr) & " "
         Next xFNum
         xNumber = Trim(Left(xNumber, xDP - 1))
   End If
   xCnt = 0
   xResult = ""
   xT = ""
   xLen = 0
   xLen = Int(Len(Str(xNumber)) / 3)
   If (Len(Str(xNumber)) Mod 3) = 0 Then xLen = xLen - 1
      Do While xNumber <> ""
      If xLen = xCnt Then
         xT = GetHundredsDigits(Right(xNumber, 3), False)
      Else
         If xCnt = 0 Then
            xT = GetHundredsDigits(Right(xNumber, 3), True)
         Else
            xT = GetHundredsDigits(Right(xNumber, 3), False)
         End If
      End If
      If xT <> "" Then
         xResult = xT & xP(xCnt) & xResult
      End If
      If Len(xNumber) > 3 Then
         xNumber = Left(xNumber, Len(xNumber) - 3)
      Else
         xNumber = ""
      End If
   xCnt = xCnt + 1
   Loop
   xResult = xResult & xPoint
   NumberstoWords = xResult
End Function
Function GetHundredsDigits(xHDgt, xB As Boolean)
   Dim xRStr As String
   Dim xStrNum As String
   Dim xStr As String
   Dim xI As Integer
   Dim xBB As Boolean
   xStrNum = xHDgt
   xRStr = ""
   On Error Resume Next
   xBB = True
   If Val(xStrNum) = 0 Then Exit Function
      xStrNum = Right("000" & xStrNum, 3)
      xStr = Mid(xStrNum, 1, 1)
      If xStr <> "0" Then
         xRStr = GetDigits(Mid(xStrNum, 1, 1)) & "Hundred "
      Else
         If xB Then
            xRStr = "and "
            xBB = False
         Else
            xRStr = " "
            xBB = False
         End If
      End If
   If Mid(xStrNum, 2, 2) <> "00" Then
      xRStr = xRStr & GetTenDigits(Mid(xStrNum, 2, 2), xBB)
   End If
   GetHundredsDigits = xRStr
End Function
Function GetTenDigits(xTDgt, xB As Boolean)
   Dim xStr As String
   Dim xI As Integer
   Dim xArr_1() As Variant
   Dim xArr_2() As Variant
   Dim xT As Boolean
   xArr_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen
   ", "Nineteen ")
   xArr_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
   xStr = ""
   xT = True
   On Error Resume Next
   If Val(Left(xTDgt, 1)) = 1 Then
      xI = Val(Right(xTDgt, 1))
      If xB Then xStr = "and "
         xStr = xStr & xArr_1(xI)
      Else
         xI = Val(Left(xTDgt, 1))
         If Val(Left(xTDgt, 1)) > 1 Then
            If xB Then xStr = "and "
               xStr = xStr & xArr_2(Val(Left(xTDgt, 1)))
               xT = False
            End If
            If xStr = "" Then
               If xB Then
                  xStr = "and "
               End If
            End If
            If Right(xTDgt, 1) <> "0" Then
               xStr = xStr & GetDigits(Right(xTDgt, 1))
            End If
         End If
      GetTenDigits = xStr
End Function
Function GetDigits(xDgt)
   Dim xStr As String
   Dim xArr_1() As Variant
   xArr_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
   xStr = ""
   On Error Resume Next
   xStr = xArr_1(Val(xDgt))
   GetDigits = xStr
End Function

请参见下面的屏幕截图。