Force preceeding spaces

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!

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!

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!

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!

If Charlie's solution doesn't work for you, you might also take a look at
creating 2 new columns:
- the first as I described above
- the second ( a hidden col which concatenates the Amt and Unit columns -
=Amt&Unit- formatted as desired)

the 2nd column is then linked to your VSAM

"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!