always print out 6 digits following specific string

Question

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).

I want my code to add a zero after the "TL-" until it contains 6 digits.

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

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.

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

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?

CURRENT ATTEMPTS GIVEN COMMENTS:

Code gets values from under the header "CUTTING TOOL" in the ws (worksheet) and prints it to the StartSht (workbook with code)

(1) Returns error on Trim line saying in valid procedure or argument

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) Runs fully but does not change the values

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 document looks like this

      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

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

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 > 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)

output of StartSht:

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

I want to add a line str = StartSht.Range(''set correct range here'') and then code to make the StartSht look like this

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

Answer 1

Put this in a new module:

Option Explicit

Public Function getDigits(strInput As String) As String

    Dim strOutput As String
    Dim strCharacter As String
    Dim i As Integer

    strOutput = ""

    For i = 1 To Len(strInput)
        strCharacter = Mid(strInput, i, 1)
        If strCharacter >= "0" And strCharacter <= "9" Then
            strOutput = strOutput & strCharacter
        End If
    Next

    getDigits = strOutput

End Function

Public Function addZeros(strInput As String) As String

    Dim intCurrentLength As Integer
    Dim strNumber As String
    Dim i As Integer

    strNumber = getDigits(strInput)
    intCurrentLength = Len(strNumber)

    If intCurrentLength < 6 Then

        For i = 1 To 6 - intCurrentLength
            strNumber = "0" & strNumber
        Next i

    End If

    addZeros = "TL-" & strNumber

End Function

Then just run addZeros([your string here]) to convert to the required format.

---

(for user4888 in the comments of this question; an example of how to check whether 'TL' is in a string. This checks cells A1 to A10, and populates a 1 or a 0 in the corresponding cell in column B depending on whether there is a 'TL' in the cell)

Private Sub TLcheck()

    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet

    For i = 1 To 10
        ws.Cells(i, 2) = InStr(1, ws.Cells(i, 1), "TL")
    Next i

End Sub

Answer 2

Expanding on Orphid's anwswer to include the 6 digits:

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

This is going to write 'ret' in column B beside the original. The sheet you are working on needs to be active when this runs because as you can see I didn't specify which Sheet was to be used. You can do that yourself if it's necessary. I assumed it only needed to be done on 1 worksheet of 1 workbook for this. Let me know if i was wrong.

Answer 3

What have you tried so far? Do you have any code to show us?

This should be a starting point, you'll need to strip out spaces and loop through the whole file of course.

Public Sub PaddingTest()
Dim PaddingArray() As String
Dim PaddingVar As String
PaddingArray() = Split(Range("A1").Value, "-", 2, vbTextCompare)
PaddingVar = PaddingArray(1)
While Len(PaddingVar) < 6
    PaddingVar = "0" & PaddingVar
Wend
Range("A2").Value = PaddingArray(0) & "-" & PaddingVar
End Sub

msdn.microsoft.com for usage of Split command

Answer 4

There is a way using an excel formula:

="TL-" & TEXT(TRIM(RIGHT(A1,LEN(A1)-FIND("-",A1,1))),"000000")

Answer 5

Here is a one liner. I am grabbing the data before and after the hypen, trimming them to remove spaces, and adding the hyphen and extra 0's.

Sub splitAddZeros()
    Dim sIn, sOut As String

    sIn = "TL - 987"

    out = Trim(Left(sIn, InStr(1, sIn, "-", vbTextCompare) - 1)) & "-" & Right("000000" & Trim(Right(sIn, Len(sIn) - InStr(1, sIn, "-", vbTextCompare))), 6)

    Debug.Print out
End Sub

Answer 6

For extracting the number, it sounds like what you want is a regular expression similar to \d{1,6}. However, I've never really enjoyed working regex in VBA, so another way of extracting the number is:

Sub Test()
    Dim str as string, ret as string, tmp as string, i as integer
    str = "T- 087652" 
    for i = 1 to len(str) 'vba strings are 1-indexed
        tmp = mid(str, i, 1) 'get the character at position i
        if IsNumeric(tmp) then ret = ret + temp 'if numeric, add to the return value
    next i
    debug.print ret 'print the resulting number to the console. To convert to a number, simply assign to a variable typed as "long"
End Sub

What this does is a simple forward loop through the string, extracting every character which IsNumeric. It should ignore whitespace wherever it occurs in the string, but they shouldn't be more than one whole number per string.

For formatting the number, you probably just want to pad the string.