VBA and no lookup table
Code:
Sub Test_1()
Dim rng As Range, cel As Range
Dim startpoint As Long
With Sheets("51batch")
Set rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
For Each cel In rng
startpoint = InStrRev(cel, "_") + 2
'for test purposes write formula to column "K"
cel.Offset(, 10) = "=Mid(" & cel.Address & "," & startpoint & ", 5)"
Next cel
End With
End Sub
Assuming that works...
Consulting my buddy Google lead to this array entered formula without any vba
=MID(A2,FIND("~",SUBSTITUTE(A2,"_","~",(LEN(A2)-LEN(SUBSTITUTE(A2,"_","")))/LEN("_")))+2,5)
I must confess I don't understand it but the establishing of where to find the last underscore comes from an old posting by the very knowledgeable
Tom Ogilvy