format a right justified zero filled field to a prn file

[right justified and zero filled (excel)]

I need to save the excel file to a PRN file. One of the fields, according to
the spec, is a numeric filed (check amount), right aligned and zero filled
for total 10 characters in length. (0000010050 for $100.50) Would you have an
advice on the formatting?

 

[Dave Peterson]

Saved from a previous post:

You could concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Drag it down the column to get all that fixed width stuff.

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/[email protected]

===============
You may need something like:

...&text(a1*100,rept(0,10))&...

 

[Rick Rothstein \(MVP - VB\)]

You can't use Format Cells to format the decimal point away, so you will
need to use VB code to accomplish what you want. Here is some event code
that will allow you to toggle the column with your values in it back and
forth between the currency and your 10-digit leading zeroes format. I used
the BeforeRightClick event for the toggle mechanism... just click into your
column and right click the mouse. You will set the column by changing the
number 3 I used in the Const statement to whatever column number you need it
to be. To implement this event procedure, right click the tab for the
worksheet containing your values and copy/paste the code below into the code
window that appeared.

Rick

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Dim X As Long
Dim LastRow As Long
Const Col As Long = 3
If Target.Column = Col Then
Cancel = True
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
For X = 1 To LastRow
With Cells(X, Col)
If InStr(.Value, ".") > 0 Then
.Value = 100 * .Value
.NumberFormat = "0000000000"
Else
.Value = .Value / 100
.NumberFormat = "$###0.00"
End If
End With
Next
End If
End Sub


"right justified and zero filled (excel)" <right justified and zero filled
(excel)@discussions.microsoft.com> wrote in message
news:[email protected]...

 

[right justified and zero filled (excel)]

It worked out perfectly. Thanks.

You can't use Format Cells to format the decimal point away, so you will
need to use VB code to accomplish what you want. Here is some event code
that will allow you to toggle the column with your values in it back and
forth between the currency and your 10-digit leading zeroes format. I used
the BeforeRightClick event for the toggle mechanism... just click into your
column and right click the mouse. You will set the column by changing the
number 3 I used in the Const statement to whatever column number you need it
to be. To implement this event procedure, right click the tab for the
worksheet containing your values and copy/paste the code below into the code
window that appeared.

Rick

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Dim X As Long
Dim LastRow As Long
Const Col As Long = 3
If Target.Column = Col Then
Cancel = True
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
For X = 1 To LastRow
With Cells(X, Col)
If InStr(.Value, ".") > 0 Then
.Value = 100 * .Value
.NumberFormat = "0000000000"
Else
.Value = .Value / 100
.NumberFormat = "$###0.00"
End If
End With
Next
End If
End Sub


"right justified and zero filled (excel)" <right justified and zero filled
(excel)@discussions.microsoft.com> wrote in message

 

[right justified and zero filled (excel)]

....&text(a1*100,rept(0,10))&... will do. I found out the "paste special
format" from another cell with custom format of "0000000000" is doing the job
as well. Thanks for the solutions.