On Mon, 4 Feb 2013 23:37:14 +0000, TimLeonard wrote:

ActiveCell.Value = txt1 & Right(ActiveCell.Offset(1,0).Value, Len(ActiveCell.Offset(1, 0).Value) - xNum + 1) - 1

When you do the subtraction operation in the above line, the result is an unformatted number for that portion of the string. If you need it to always be padded to three digits, you need it to return a string.
e.g:

ActiveCell.Value = txt1 & Format(Right(ActiveCell.Offset(1, 0).Value, _
Len(ActiveCell.Offset(1, 0).Value) - xNum + 1) - 1, "000")