我有很多名称为“ TL-”的字符串,后跟6位数字(即TL-000456,TL-000598)。有时会打印出少于6位的数字(例如TL-09872,TL-345,TL-02)。

我希望我的代码在“ TL-”之后添加一个零,直到它包含6位数字为止。

Start:           Output:
TL-000456   ->   TL-000456
TL-000598   ->   TL-000598
TL-09872    ->   TL-009872
TL-345      ->   TL-000345
TL-02       ->   TL-000002


如果可能的话,我希望这样做,以便即使字符串中包含空格(即“ TL-”,“ TL-”),也始终会抓住6位数字。

TL - 987    ->   TL-000987
TL- 839     ->   TL-000839


我的代码中有一个函数,该函数会修剪“ TL”值,以在分号或逗号之前获取所有内容,因此理想情况下,代码会放入其中。有什么想法吗?

当前尝试给出的评论:

代码从ws(工作表)的标题“ CUTTING TOOL”下获取值,并将其打印到StartSht(带有代码的工作簿)

(1)在有效程序或自变量中的Trim行上返回错误

With WB
  For Each ws In .Worksheets

Dim sIn, sOut As String
    'find CUTTING TOOL on the source sheet
    If Not ws.Range("A1:M15").Find(What:="CUTTING TOOL", LookAt:=xlWhole, LookIn:=xlValues) Is Nothing Then
    Set hc = ws.Range("A1:M15").Find(What:="CUTTING TOOL", LookAt:=xlWhole, LookIn:=xlValues)
        Set dict = GetValues(hc.Offset(1, 0), "SplitMe")
        If dict.count > 0 Then
        'add the values to the workbook, column 3
            Set d = StartSht.Cells(Rows.count, hc2.Column).End(xlUp).Offset(1, 0)
            d.Resize(dict.count, 1).Value = Application.Transpose(dict.items)


            'trim values **implement new code here**
            With StartSht
                Trim (Left(sIn, InStr(1, sIn, "-", vbTextCompare) - 1)) & "-" & Right("000000" & Trim(Right(sIn, Len(sIn) - InStr(1, sIn, "-", vbTextCompare))), 6)
            End With


(2)完全运行但不更改值

With WB
  For Each ws In .Worksheets
        'find CUTTING TOOL on the source sheet
        If Not ws.Range("A1:M15").Find(What:="CUTTING TOOL", LookAt:=xlWhole, LookIn:=xlValues) Is Nothing Then
        Set hc = ws.Range("A1:M15").Find(What:="CUTTING TOOL", LookAt:=xlWhole, LookIn:=xlValues)
            Set dict = GetValues(hc.Offset(1, 0), "SplitMe")
            If dict.count > 0 Then
            'add the values to the master list, column 3
                Set d = StartSht.Cells(Rows.count, hc2.Column).End(xlUp).Offset(1, 0)
                d.Resize(dict.count, 1).Value = Application.Transpose(dict.items)

                Dim str As String, ret As String, tmp As String, j As Integer
                With StartSht
                For j = 1 To Len(str)
                    tmp = Mid(str, j, 1)
                    If IsNumeric(tmp) Then ret = ret + tmp
                Next j

                For j = Len(ret) + 1 To 6
                    ret = "0" & ret
                Next

                Debug.Print ret


                End With




StartSht Excel文档如下所示

      A            B                C                  D
1    TDS        HOLDER        CUTTING TOOL        File Name
2   TDS-1         H1            TL-000289          TDS-1.xlsx
3   TDS-2         H2            TL-000274          TDS-2.xlsx
4   TDS-3         H3            TL-0002            TDS-3.xlsx
5   TDS-4         H4            TL-0343            TDS-4.xlsx


在下面的“剪切工具”代码之后,它看起来像代码下面的输出,因为这是我获取信息的第一部分

码:

            With WB
                For Each ws In .Worksheets
                'find CUTTING TOOL on the source sheet
                If Not ws.Range("A1:M15").Find(What:="CUTTING TOOL", LookAt:=xlWhole, LookIn:=xlValues) Is Nothing Then
                Set hc = ws.Range("A1:M15").Find(What:="CUTTING TOOL", LookAt:=xlWhole, LookIn:=xlValues)
                    Set dict = GetValues(hc.Offset(1, 0), "SplitMe")
                    If dict.count > 0 Then
                    'add the values to the master list, column 3
                        Set d = StartSht.Cells(Rows.count, hc2.Column).End(xlUp).Offset(1, 0)
                        d.Resize(dict.count, 1).Value = Application.Transpose(dict.items)


StartSht的输出:

      A            B                C                  D
1    TDS        HOLDER        CUTTING TOOL        File Name
2                              TL-000289
3                              TL-000274
4                              TL-0002
5                              TL-0343


我想添加一行str = StartSht.Range(``在此处设置正确的范围''),然后编写代码以使StartSht看起来像这样

      A            B                C                  D
1    TDS        HOLDER        CUTTING TOOL        File Name
2                              TL-000289
3                              TL-000274
4                              TL-000002
5                              TL-000343

最佳答案

扩展Orphid的答案以包括6位数字:

Sub Test()
    Dim str as string, ret as string, tmp as string, i as integer, j as integer

    for j = 2 to StartSht.Range("C2").End(xlDown).Row
        ret = ""
        str = StartSht.Range("C" & j).Value
            for i = 1 to len(str)
                  tmp = mid(str, i, 1)
                  if IsNumeric(tmp) then ret = ret + tmp
            next i

            For i = Len(ret) + 1 To 6
                ret = "0" & ret
            Next
            ret = "TL-" & ret
            StartSht.Range("C" & j).Value = ret
    next j
End Sub


这将在原始文档旁边的B列中写入“ ret”。您正在处理的工作表在运行时需要处于活动状态,因为您可以看到我没有指定要使用的工作表。如果有必要,您可以自己做。我假设只需要为此在1个工作簿的1个工作表上完成。让我知道我是否错了。

关于vba - 始终在特定字符串后打印出6位数字,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/31143964/

10-15 02:28