excel - 将 10M 行带有字母的文本转换为数字

Question

我需要将“文本数字”转换为数字。文本编号如下所示:32,23B 或 242,23M。
我需要删除字母并乘以 1,000 ； 1,000,000；等等，取决于字母。
下面的代码有效，但由于我需要转换 10M 行，所以需要很长时间。
有什么聪明的方法吗？

For shIndex = startSheet To lastSheet
    LastRowF = Sheets(shIndex).Range("F" & Rows.Count).End(xlUp).Row
        
    For i = 3 To LastRowF
        
        Mul = Right(Sheets(shIndex).Range("F" & i), 1)
            
        Select Case Mul
            Case "K"
                Multiplier = 1
            Case "M"
                Multiplier = 1000
            Case "B"
                Multiplier = 1000000
            Case "T"
                Multiplier = 1000000000
            Case Else
                Multiplier = False
        End Select
            
        If Multiplier Then
            With Sheets(shIndex)
                .Range("F" & i) = CSng(Left(Sheets(shIndex).Range("F" & i), Len(Sheets(shIndex).Range("F" & i)) - 1) * Multiplier)
                .Range("F" & i).NumberFormat = "#,##0"
            End With
        End If
    Next i
Next shIndex

Answer 1

使用 Variant数组，在内存中进行转换，然后将整个数组写回工作表:

For shIndex = startSheet To lastSheet
    LastRowF = Sheets(shIndex).Range("F" & Rows.Count).End(xlUp).Row

    Dim data() As Variant
    data = Sheets(shIndex).Range("F3:F" & LastRowF).Value

    Dim i As Long
    For i = LBound(data, 1) to UBound(data, 1)
        Mul = Right(data(i, 1), 1)

        Select Case Mul
            Case "K"
                Multiplier = 1
            Case "M"
                Multiplier = 1000
            Case "B"
                Multiplier = 1000000
            Case "T"
                Multiplier = 1000000000
            Case Else
                Multiplier = False
        End Select

        If CBool(Multiplier) Then
            data(i, 1) = Left(data(i,1), Len(data(i, 1)) - 1) * Multiplier
        End 

        With Sheets(shIndex).Range("F3:F" & LastRowF)
           .Value = data
           .NumberFormat = "#,##0"
        End With 
     
    Next
Next