excel - 将文本字符串中的数字相乘。 +综述

Question

概述:

A1 = Dog_dkp_1、Cat_dkp_50、Mouse_dkp_20

=Nmult(A1,3)

将单元格 A1 中的数字乘以 3。你会得到:

Dog_dkp_3、Cat_dkp_150、Mouse_dkp_60

我可以使用可选的舍入参数。

=Nmult(A1,1.1,0)

=Nmult(目标、乘数、向上舍入到点)

结果:Dog_dkp_2、Cat_dkp_55、Mouse_dkp_22

...一开始就不是我的...

Function Nmult(text As String, multiplier As Double)

Dim i As Long, char As String, numb As String
Application.Volatile True
For i = 1 To Len(text)
    char = Mid(text, i, 1)
    If char Like "#" Then
        While char Like "#"
            numb = numb + char
            i = i + 1
            char = Mid(text, i, 1)
        Wend
        char = Str((CLng(numb) * multiplier)) + char
        numb = Empty
    End If
    Nmult = Nmult + char
Next i

End Function

Answer 1

只需进行一些小的更改即可达到您的要求。我在函数定义中添加了一个选项变量，并在计算中添加了 RoundUp 函数。

Function Nmult(text As String, multiplier As Double, Optional roundValue As Double)
    Dim i As Long, char As String, numb As String
    Application.Volatile True
    For i = 1 To Len(text)
        char = Mid(text, i, 1)
        If char Like "#" Then
            While char Like "#"
                numb = numb + char
                i = i + 1
                char = Mid(text, i, 1)
            Wend

            char = Str(Application.RoundUp((CLng(numb) * multiplier), roundValue)) + char
            numb = Empty
        End If
        Nmult = Nmult + char
    Next i
End Function

<小时/>

测试:(B 列显示相邻 A 列单元格中的公式

<小时/>

编辑

CLng 向结果添加前导空格。去除它的一种方法是修剪绳子。更改此行:

char = Str(Application.RoundUp((CLng(numb) * multiplier), roundValue)) + char

对此:

char = LTrim$(Str(Application.RoundUp((CLng(numb) * multiplier), roundValue)) + char)