The following code was provided as a solution to this issue.
Ideally what I would like is to look at column D and if it is not blank "" then look at column J and take the first 5 digits and put them into column O
What does the use of the .Text in line 6 provide, given that all we are seeking is the first 5 characters of the cell value?Sub Test() Dim c As Range With Sheets("Sheet1") 'amend as appropriate For Each c In .Range("D2:D" & .Cells(Rows.Count, "D").End(xlUp).Row) 'assumed header in row 1 If Not IsEmpty(c.Value) Then c.Offset(, 11).Value = Left$(c.Offset(, 6).Text, 5) End If Next c End With End Sub