If you mean the total length of the string (numeric value and units) is
supposed to be eight characters then try something like this:

Dim MyAmt As String
Dim MyFmt As String

MyAmt = "21.7 SF"
MyFmt = Format(Val(MyAmt)) & " " & Split(MyAmt)(1)
If Len(MyFmt) < 8 Then MyFmt = Space(8 - Len(MyFmt)) & MyFmt


"cottage6" wrote:

> I thought about that but I can't separate the column because the VSAM file is
> looking for one column only. If I separate the column a programmer will need
> to go in a modify VSAM.
>
> "gocush" wrote:
>
> > first you should separate the Unit label (OZ, SF etc) and create a new column
> > for this. That leaves this col for just the numeric amount.
> >
> > You can then do a custom format on this column. Something like:
> >
> > sub Testformat()
> > Dim MyAmt as Long
> >
> > '........
> > Range("B5")=Format(MyAmt,"########")
> > '.........
> > End Sub
> >
> > "cottage6" wrote:
> >
> > > I'm having a problem with a size field not lining up properly when the data
> > > gets transferred to a VSAM file. The field is an 8-digit field, and I need
> > > to force preceeding spaces before each size entry in the range. Entries can
> > > be varying lengths such as 40.5 OZ, 238.3 SF, 16 OZ, and so on. I think I
> > > can come up with a way to do this using a formula, but I'd rather know the
> > > proper way to do this using VB. Any help is greatly appreciated!