A simplified example of what I need to do.

I have the following cells in Excel A1=xxx, A2=yyyyy and A3=zzzzz
which I need to convert in a fixed length record with offsets of 0, 5 & 13
so that it looks like this "xxx yyyyy zzzzz"

If the field is a numeric I can use custom option to format the field to the
appropriate no. of characters but how do I do this for an alpha field ?

you can use the LEFT function

eg

Sub testpad()
MsgBox Pad("AB", 5) & "def"
msgbox PAD(range("A1"),5)
End Sub

Function Pad(text As String, chrs As Long) As String
Pad = Left(text & String(chrs, " "), 5)
End Function

"Gavin" wrote:

A simplified example of what I need to do.

I have the following cells in Excel A1=xxx, A2=yyyyy and A3=zzzzz
which I need to convert in a fixed length record with offsets of 0, 5 & 13
so that it looks like this "xxx yyyyy zzzzz"

If the field is a numeric I can use custom option to format the field to the
appropriate no. of characters but how do I do this for an alpha field ?