return TEXT value from MID w integer results

[manster_mg]

Hi, the code below is being used to extract integer values from a string but I need the returned values to be formatted as TEXT with 4 characters i.e. "0000".
I'm new to Excel VBA so I'm not sure how to modify the code to do this.
I appreciate any help. Thank you

Sub MID_VNUM()


    Dim LastRow As Long
    Dim i As Long
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = 3 To LastRow
    Cells(i, 1).Value = Mid(Cells(i, 2).Value, 1, InStr(1, Cells(i, 2).Value, "-") - 2)
    Next i
    
    
End Sub

[June7]

Try:

Cells(i, 1).Value = "'" & Format(Mid(Cells(i, 2).Value, 1, InStr(1, Cells(i, 2).Value, "-") - 2), "0000")

[arnelgp]

to make your Sub more robust, check if there is indeed a dash (-) on the cell:

Sub MID_VNUM()




    Dim LastRow As Long
    Dim i As Long, j as Integer
    dim s As string
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = 3 To LastRow
    s = Cells(i, 2).Value & ""
    j = InStr(1, s, "-")
    If j <> 0 Then
            Cells(i, 1).Value = Trim$(Mid$(s, 1, j - 1))
    End If
    Next i
    
    
End Sub

[manster_mg]

Thanks, it worked. I didn't see the apostrophe between the quotation marks at first but then it clicked. Thank you