Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

Exporting Fixed-Width Text From Excel

I'm working with Excel 98 on a Mac.  I'm having the darndest time figuring out how to export fixed-width text.  The only way I've come across is to get the file into Access and go from there.  But I don't have Access, which is a problem.  I have Filemaker, but I don't think that can do it either.  I'm sure it could be done with a macro, but I think that's beyond my skills in that area.  Thanks in advance for the help.  ~Sky
0
SkyKing162
Asked:
SkyKing162
  • 3
  • 3
  • 2
1 Solution
 
weedCommented:
Fixed width text? That's a property of the font used. Nothing to do with excel, access, or filemaker. Just export it as text, and change the font to a fixed width typeface.
0
 
SkyKing162Author Commented:
Ok, maybe I wasn't as clear as I should have been.  I want to format my Excel spreadsheet such that when I save it as space-delimited and view it with fixed-width font it will appear as a table without any extra formatting.  If I have a column in Excel that has a list of names, I want Excel to export each entry from that column with as many characters as the longest entry, adding an appropriate number of blank spaces onto any names that are shorter than the longest name so that every entry contains the same number of characters.  Make more sense?
0
 
weedCommented:
Good luck. The closest youll get is an HTML formatted table.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SkyKing162Author Commented:
No one out there has a macro that will do this?
0
 
byundtCommented:
SkyKing162,

Don't know if you still care about the solution to the problem, but I had some free time today and ...

The following macro will find the longest length text string in a column. It then pads all cells in that column with enough blanks (on the right) so all cells have the same number of characters. Width may vary from one column to the next, but each column will be exactly as wide as it needs to be.

Note that the macro is set up for text rather than numbers. This may not bother you (since your problem statement spoke only of names), but numbers are right aligned in the space delimited document.

I tested it with Excel 98 and pasted the padded results into a blank SimpleText document. After converting the font to Monaco, everything seemed to line up nicely. I also tested it by saving as space delimited text, then opening that file in SimpleText. After converting the font to Monaco, everything seemed to line up nicely once again.

To install the macro, use the Tools...Macros...VBA Editor menu, then double-click Sheet1 and paste the text.

To run the macro, OPT + F8 to bring up the list of macros available, select Sheet1.FixedWidth and click the Run button.

If you no longer care about the answer to your question, then please send a note to the moderator (in the community section) to withdraw the question.

Brad


Option Explicit
Sub FixedWidth()
Dim i As Integer, MaxLen As Integer, nCols As Integer, nRows As Integer, _
    myCell As Range, myColumn As Range, myData As Range, myLen As Integer, temp As String
Set myData = ActiveSheet.UsedRange
nRows = myData.Rows.Count
nCols = myData.Columns.Count
For Each myColumn In myData.Columns
    MaxLen = 0
    For Each myCell In myColumn.Cells
        temp = myCell.Value
        MaxLen = Application.Max(MaxLen, Len(temp))
    Next myCell
    For Each myCell In myColumn.Cells
        myLen = Len(myCell.Value)
        If myLen < MaxLen Then
            For i = myLen + 1 To MaxLen
                myCell = myCell & " "
            Next i
        Else
        End If
    Next myCell
Next myColumn
End Sub
0
 
SkyKing162Author Commented:
That's awesome.  Could that be easily modified to handle numbers, too?  I come across problems if I format numbers as text, because then I get all the unwanted decimal places.
0
 
byundtCommented:
SkyKing162,

Please post an example of what you have for data, and how you want it to appear.

Brad
0
 
byundtCommented:
SkyKing162,

The following macro will pad text with blanks to the right and numbers with blanks to the left. It assumes that all numbers are integers (it ignores digits after the decimal point).

I tested the macro on Excel 2000, so please advise if it doesn't recognize a keyword.

Option Explicit
Sub FixedWidth()
Dim i As Integer, MaxLen As Integer, _
    myCell As Range, myColumn As Range, myData As Range, myLen As Integer, temp As String
Set myData = ActiveSheet.UsedRange
For Each myColumn In myData.Columns
    MaxLen = 0
    For Each myCell In myColumn.Cells
        temp = myCell.Value
        MaxLen = Application.Max(MaxLen, Len(temp))
    Next myCell
    For Each myCell In myColumn.Cells
        If IsNumeric(myCell) Then
            temp = Format(myCell, "#")
            myCell.NumberFormat = "@"
            myCell = Application.Rept(" ", MaxLen - Len(temp)) & temp
        End If
        myLen = Len(myCell.Value)
        If myLen < MaxLen Then myCell = myCell & Space(MaxLen - myLen)
    Next myCell
Next myColumn
End Sub
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now