Space Format From Excel to Notepad

  • Thread starter Thread starter ms.maryw
  • Start date Start date
M

ms.maryw

I have an Excel file I need to save as notepad. I am able to save the
file however I need there to be a specific number of charaters for
each field. The first field should allow for 10 characters then 20
then 45. If there is data then the remaining should be followed by
spaces.

I need the notepad data to look like this:
1-10 1-20 1-45
Smith Henry Minnesota
Jane Doe California

It looks like this:
Smith Henry Minnesota
Jane Doe California

Any ideas? Thank you for your help.
 
You can use a second sheet to effectively copy the first but with the
required spaces tagged on to the end. Put this formula in A1 of
Sheet2:

=Sheet1!A1&REPT(" ",10-LEN(Sheet1!A1))

and in B1:

=Sheet1!B1&REPT(" ",20-LEN(Sheet1!B1))

and in C1:

=Sheet1!C1&REPT(" ",45-LEN(Sheet1!C1))

and so on. Then copy these down as far as you have data in Sheet1.
Then you can fix the values and dispense with Sheet1.

Hope this helps.

Pete
 
Saved from a previous post:

There's a limit of 240 characters per line when you do File|saveas and use .prn
files. So if your data wouldn't create a record that was longer than 240
characters, you can save the file as .prn.

I like to use a fixed width font (courier new) and adjust the column widths
manually. But this can take a while to get it perfect. (Save it, check the
output in a text editor, back to excel, adjust, save, and recheck in that text
editor. Lather, rinse, and repeat!)

Alternatively, 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]
 
Back
Top