?
Solved

Exporting Fixed-Width Text From Excel

Posted on 2003-02-28
10
Medium Priority
?
362 Views
Last Modified: 2010-04-25
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
Comment
Question by:SkyKing162
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
10 Comments
 
LVL 30

Expert Comment

by:weed
ID: 8044043
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
 

Author Comment

by:SkyKing162
ID: 8054476
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
 
LVL 30

Expert Comment

by:weed
ID: 8055424
Good luck. The closest youll get is an HTML formatted table.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

Author Comment

by:SkyKing162
ID: 8057808
No one out there has a macro that will do this?
0
 
LVL 81

Accepted Solution

by:
byundt earned 300 total points
ID: 8406531
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
 

Author Comment

by:SkyKing162
ID: 8651486
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
 
LVL 81

Expert Comment

by:byundt
ID: 8656481
SkyKing162,

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

Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 8663085
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Yesterday Apple introduced their revolutionary new iOS 5 operating system that claims to be the ultimate mobile technology interface. One of the biggest innovations of the new operating system is the introduction of the iCloud computing network. Thi…
Steve Jobs once said that Blu-ray is "a bag of hurt". As Macs users well known, things haven’t settled down (at least not from Apple’s perspective). Several years after that comment, Mac OS X still doesn’t support Blu-ray playback, nor has any Mac s…
Users will learn how to set proper sequence settings, scale images, paste attributes, add transitions, fades, and music. Open up Final Cut Pro 7 and Create a new Project: Set the Sequence Settings. a) Click File > Easy Setup > Format > Apple ProRe…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question