填充零位数字

I have a loop like this to save sheets as CSV files but my first 9 sheets are name liked sinani-01 ... sinani-09 (not like sinani-1 ... sinani-9). How I can concatenate a 0 only before numbers less than 10?

我有一个像这样的循环来保存工作表作为CSV文件,但我的前9页是名字像sinani-01 ... sinani-09(不像sinani-1 ... sinani-9)。如何在数字小于10之前连接0?

Sub Adder()
    Dim animal As String
    Dim i As Integer
    For i = 1 To 120
        animal = "sinani-" & i
        Sheets(animal).SaveAs "E:\Data\CSV\" & animal & ".csv", xlCSV
    Next i
End Sub

4 个解决方案

#1

VBA has a Format() function that you can use to pad numbers.

VBA有一个Format()函数,可用于填充数字。

animal = "sinani-" & Format$(i, "00")

This will pad single-digit numbers with a 0. Your two- and three-digit numbers will continue to work as expected.

这将使用0填充一位数字。您的两位和三位数字将继续按预期工作。

#2

In the fifth line use the Format function like this:

在第五行中使用Format函数,如下所示:

animal = "sinani-" & Format(i, "#00")

The # means optionally a digit (i.e. present only if there are that many digits in i), 0 means definitely a digit, whereby leading zeros are used if i hasn't got enough digits.

#表示可选的数字(即只有在i中有多个数字时才出现),0表示绝对是一个数字,如果我没有足够的数字,则使用前导零。

#3

Concatenate with a leading series of zeroes and peel off as many digits from the right-hand side as you need.

与一系列领先的零点连接,并根据需要从右侧剥离尽可能多的数字。

animal = "sinani-" & Right("00" & i, 2)

'alternate for many leading zeroes (e.g. DUNS number)
animal = "sinani-" & Right(String(9, "0") & i, 9)

#4

Replace & i by & IIF(i < 10,"0","") & i

替换&i by&IIF(i <10,“0”,“”)&i

On edit: Even though in this case Format provides a cleaner solution than IIF, the IIF trick has some other uses in tweaking output. For example, if you wanted to inform the user how many cells were found which satisfy some condition you could use something like

在编辑时:即使在这种情况下,Format提供了比IIF更清晰的解决方案,IIF技巧在调整输出方面还有一些其他用途。例如,如果您想通知用户有多少单元格可以满足某些条件,您可以使用类似的东西

MsgBox n & "cell" & IIF(n <> 1,"s","") & " found"

to gracefully handle plural vs. singular endings

优雅地处理复数与单数结尾

#1