title: always print out 6 digits following specific string
body: <p>I have many strings with the name "TL-" followed by 6 digits (ie TL-000456, TL-000598). Sometimes it will print out having fewer than 6 digits (ie TL-09872, TL-345, TL-02). </p>
  
  <p><strong>I want my code to add a zero after the "TL-" until it contains 6 digits.</strong></p>
  
  <pre><code>Start:           Output:
  TL-000456   -&gt;   TL-000456
  TL-000598   -&gt;   TL-000598
  TL-09872    -&gt;   TL-009872
  TL-345      -&gt;   TL-000345
  TL-02       -&gt;   TL-000002
  </code></pre>
  
  <p>If possible, I would like it to do this so that even if a space is included in the string (ie "TL - ", "TL -"), 6 digits would always be grabbed.</p>
  
  <pre><code>TL - 987    -&gt;   TL-000987
  TL- 839     -&gt;   TL-000839
  </code></pre>
  
  <p>I have a function in my code which trims the "TL" values to get everything before a semicolon or comma so ideally the code would go in there. Thoughts?</p>
  
  <p><strong>CURRENT ATTEMPTS GIVEN COMMENTS:</strong></p>
  
  <p>Code gets values from under the header "CUTTING TOOL" in the ws (worksheet) and prints it to the StartSht (workbook with code)</p>
  
  <p>(1) Returns error on <code>Trim</code> line saying in valid procedure or argument</p>
  
  <pre><code>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 &gt; 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)) &amp; "-" &amp; Right("000000" &amp; Trim(Right(sIn, Len(sIn) - InStr(1, sIn, "-", vbTextCompare))), 6)
              End With
  </code></pre>
  
  <p>(2) Runs fully but does not change the values</p>
  
  <pre><code>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 &gt; 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" &amp; ret
                  Next
  
                  Debug.Print ret
  
  
                  End With
  </code></pre>
  
  <hr>
  
  <p>StartSht Excel document looks like this</p>
  
  <pre><code>      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
  </code></pre>
  
  <p>after the "CUTTING TOOL" code I have below, it just looks like the output below the code because that is the first section I grab information for</p>
  
  <p>CODE:</p>
  
  <pre><code>            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 &gt; 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)
  </code></pre>
  
  <p>output of StartSht:</p>
  
  <pre><code>      A            B                C                  D
  1    TDS        HOLDER        CUTTING TOOL        File Name
  2                              TL-000289          
  3                              TL-000274          
  4                              TL-0002            
  5                              TL-0343     
  </code></pre>
  
  <p>I want to add a line str = StartSht.Range(''set correct range here'') and then code to make the StartSht look like this</p>
  
  <pre><code>      A            B                C                  D
  1    TDS        HOLDER        CUTTING TOOL        File Name
  2                              TL-000289          
  3                              TL-000274          
  4                              TL-000002            
  5                              TL-000343     
  </code></pre>