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