Space Format From Excel to Notepad

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.
0
ms.maryw (2)
12/2/2010 5:43:30 PM
excel 39879 articles. 2 followers. Follow

2 Replies
872 Views

Similar Articles

[PageSpeed] 36

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:

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

and in B1:

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

and in C1:

=3DSheet1!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

On Dec 2, 5:43=A0pm, "ms.maryw" <ms.ma...@gmail.com> wrote:
> 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 =A0 =A0 =A0 1-20 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 1-45
> Smith =A0 =A0 Henry =A0 =A0 =A0 =A0 =A0 =A0 =A0 Minnesota
> Jane =A0 =A0 =A0 Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 California
>
> It looks like this:
> Smith Henry Minnesota
> Jane Doe California
>
> Any ideas? Thank you for your help.

0
pashurst (2576)
12/2/2010 7:46:57 PM
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/groups?threadm=015b01c32c5f%24b3d398d0%24a501280a%40phx.gbl

On 12/02/2010 11:43, ms.maryw wrote:
> 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.

-- 
Dave Peterson
0
petersod1 (224)
12/2/2010 8:42:01 PM
Reply:

Similar Artilces:

Excel 97 dropdown function box
The dropdown function box on the Formula Bar show formulas but will no longer show "More Functions". How can I get this item back? ...

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

Format Issue of Windows Mail and M.S. Group
Hi: When clicking to open any mails including this site, MS Newsgroup, top right pane of 'X' part is not visible. In the past, I used 'Shove-It' program that was found on the web-site, priced $15 that corrected this issue. However, this program is no longer effective, thus it went back to the original format, .... not fully opening content of mail of right side of pane, although I can read any mails by using 'maximizing bvutton' to open in full. Any imputs on this, above would be greatly appreciated. Thanks, "Winter10" <Winter10No...

generate report thru pracle to excell
Recently i upgraded to ms office 2003 since then I'm not able to generate reports thru oracle unto excell (online ). But when I log in an administrator in the local system I'm able to generate the report. the same does not happen with domain users ...

Date format in a report
The date is formatted in my table in yyyy/mmm/dd. In one report I need to run for another organization the format needs to be in dd/mmm/yyyy format. How can I change the way it displays in the report? I do not want to change it in the table as I generally want the format to be yyyy/mmm/dd. -- Charlie You can simply use either the format property for the control or you can use the Format() if you need to set the format to something that doesn't already exist. Format([YourDataFieldName],"dd/mmm/yyyy") -- Hope this helps, Daniel Pineault http://www.carda...

Vertical Header in Excel
Is there a way to make a header in excel run down the side of the spreadsheet (vertical header)? You can freeze panes so that certain columns are always visible. Is that what you meant? If yes, then make sure the column(s) that should be visible is showing. Then select the column to the right and window|Freeze Panes (in xl2003 menus) For instance if I wanted column A to be always visible, I would make sure column A is visible to start. Then select column B and window|freeze Panes. On 05/18/2010 09:09, Erika wrote: > Is there a way to make a header in excel run do...

finding the area under the curve in a graph in excel
Need help having excel calculate the total area under the curve of a graph in excel. Have a look at Bernard Liengme's website: http://www.stfx.ca/people/bliengme/ExcelTips/ HTH Jason Atlanta, GA >-----Original Message----- >Need help having excel calculate the total area under the curve of a graph in >excel. >. > ...

Excel 2003 file freezes laptop
I took a laptop away from the office and tried to open a large spreadsheet on the local C: drive. Each time I tried the laptop froze and had to be rebooted. When I connected back to the LAN at work, and opened the same file on the local C: drive I had no problems. What could cause this? If there is any corruption is there a way to decompile or something as you would in Access? Thanks, Mich ...

How to slant cells in Excel on top of chart at about 45 degrees
Trying to make a chart and slant cells at the top at about a 45 degree angle with borders and still be able to type into it. Is this possible? ckricci Wrote: > Trying to make a chart and slant cells at the top at about a 45 degree > angle > with borders and still be able to type into it. Is this possible? HI ckricci Try Format > Cells > Alignment Tab and set the Orientation to 45 degress -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2...

Pasting Graphs as Pictures in Excel
In an attempt to save file size, I would like to use paste special to past Excel graphs as pictures within Excel. I know you can paste special as pictures within PPT, but is it possible to do it within Excel???? Not sure if that can be done, what I do is cop the graph, open mspaint paste, select it again copy and paste back into excel, it does it as an image -- -John Please rate when your question is answered to help us and others know what is helpful. "andy.hogan@gmail.com" wrote: > In an attempt to save file size, I would like to use paste special to > past Excel gra...

Syntax for Re-registration of Excell
Windows 2000, Excel v8. Does anyone know the syntax for re-registration with Windows? We get a file already open error every time we launch a SS without opening the app first. Had a similiar problem with Word and fixed it with Winword /r. Thanks! Hi first you may check: http://www.contextures.com/xlfaqApp.html#AlreadyOpen -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > Windows 2000, Excel v8. Does anyone know the syntax for > re-registration with Windows? We get a file already open > error every time we launch a SS without opening t...

Displaying a dataset or datatable in Excel
There's something I think ought to be easy, but I can't find a way to do it. Either there's something wrong with the way I'm searching, or there's a reason it can't be done easily. What I want is to create an excel addin that, when the user opens a spreadsheet , it goes out and fetches some data from a database, and displays it in a table. Pretty straightforward. Here's some code: void Application_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook wb) {//I've also attached this to other events. The "open" event isn't...

Data changes when Analyzed with Excel
I have a query with 3 tables that represent master item list, count qty and as qty. When I look at the results in Query, all is well and accurate yet when I select Analyze with Excel, values change. For example, if a record shows zero qty in the as of field and zero quantity in the count field, I get a value in one or both of the fields that origianlly were zero. Other than linking the tables and grouping by master item list (to show all parts regardless of qty's), there are no formulas or expressions in this query. One of the tables is linked to a FoxPro table via ODBC driver. ...

Recovery excel file
Anybody knows if there is any free tools to recover a advanced excel file (using macro, graphics, formulas, and several formats)???????? Tks, Marcelo Rayol ...

Excel 2000/Change Default Opening Location?
I'm using Excel 2000. When I open Excel, and the click on the Open button to open worksheet, it goes to a default location I'd like to change. How can I change that default location? I have searched under Tools > Options, but find no means there. Thanks. -- ---------- CWLee Former slayer of dragons; practice now limited to sacred cows. Believing we should hire for quality, not quotas, and promote for performance, not preferences. Tools|Options|General Tab change the entry in the Default File Location box CWLee wrote: > > I'm using Excel 2000. When I open...

Printing in excel always comes out in bold even tho bold not on
When I print in excel my work always comes out in bold even though the bold is not switched on. Any suggestions?? It could be a printer setting. Does it print in bold from other applications? Regards, Fred "Scarlett50" <Scarlett50@discussions.microsoft.com> wrote in message news:DF3BAEA1-4D22-449E-BB8F-31DD8855AF20@microsoft.com... > When I print in excel my work always comes out in bold even though the > bold > is not switched on. Any suggestions?? No only when I have to type a report in excel - the actual report is ok on other computers! but...

Powerpoint with Excel formatted information
My team has an excel file with Text and Icons in a status report format. We use it to copy and insert paste into Powerpoint. After copying into Powerpoint, I try to select it and edit but powerpoint gives an error that says: "There isn't enough memory available to read the Excel file" Other team members use this same excel template and powerpoint and they can edit the information after it is copied to powerpoint. And I can use their files and they work fine. Only one team member has this issue so somehow this person's excel file is using a massi...

how do I open excel workbook If i forget my workbook password
I made programm with a lot of formuals so I've put password on the workbook, because I did not wanted to earase formulas accidently,but now I can't open it because I forget the password. Thanks for help Hi search Google for 'Excel password remover' -- Regards Frank Kabel Frankfurt, Germany "av8rart" <av8rart@discussions.microsoft.com> schrieb im Newsbeitrag news:D2443918-FEF4-41E6-92C4-9B19A293778A@microsoft.com... > I made programm with a lot of formuals so I've put password on the workbook, >...

Excel calculates wrong ????
I have had no luck figuring this out... I have a list of 8 positive and negative numbers that 2 decimal place each. The numbers sum to zero confirmed by a calculator, however exce sums them to (2.55654899E12). I do not know why excel calculates the with a small value left that is denoted by scientific notation. I checked the value of each number to ensure that it did not includ any extra decimals, and I even re-typed them. I have had this happen with several other lists of numbers that are n brainers to calculate, and excel added extra scientific notation to th total. I have no idea why ...

Output to excel ==> replace worksheet
Usually out to excel will replace a workbook.Can we make it to replace worksheet only.Thank's "shiro" <shiro@myname?.com> wrote in message news:umONlZiRIHA.4584@TK2MSFTNGP03.phx.gbl... > Usually out to excel will replace a workbook.Can we make it > to replace worksheet only.Thank's How about, just replacing the contents of a cell, or cells? Dim appXL As Object Dim wkb As Object Dim wks As Object Set appXL = CreateObject("Excel.Application") Set wkb = appXL.Workbooks.Open("C:\MyFolder\MyWorkBook.xls") Set wks = wkb.Worksheets(1) wks.Cell...

IN EXCEL, WHEN I CLICK ONA SINGLE CELL It HIGHLIGHTS WHOLE Page
please help Hi maybe: http://www.mvps.org/dmcritchie/excel/ghosting.txt -- Regards Frank Kabel Frankfurt, Germany "confused" <confused@discussions.microsoft.com> schrieb im Newsbeitrag news:36180425-96C2-4368-9DE0-3E60741154FF@microsoft.com... > please help Just click on a cell and nothing else? Or perhaps you mean click on a cell then as you move the cursor around other cells are selected like the cursor is stuck? If the former, you must have some event code which selects the cells or you are clicking on the gray box at intersect of rows and columns(top left above ro...

format negative dollars
We have 2 computers in this office, both with Office XP On one we get ($ 4.00) on the other we get -$ 4.00 This format seems to vary from file to files. Is there a way to set the default on Excel to ($ 4.00) ? Thanks Doug Doug, It might help to check the Regional Settings in the Control Panel (I assume your using windows). It has settings for currency and number displays which will affect excel. Check if yours and your co-workers settings are the same. Dan E "doug loewen" <doug@aacropolishomes.com> wrote in message news:004f01c38c25$5c483320$a401280a@phx.gbl... > W...

outlook 2003 inbox format
How can I eliminate the "Today", "Yesterday", "Last Week", etc? I much prefer the look of Outlook 2000. Thank you View > Arrange By > Show in groups "Trng Administrator" <Trng Administrator@discussions.microsoft.com> wrote in message news:49639DAB-7738-4951-B6AC-6A35AD2862FD@microsoft.com... > How can I eliminate the "Today", "Yesterday", "Last Week", etc? I much > prefer the look of Outlook 2000. > > Thank you neo [mvp outlook] wrote: > View > Arrange By > Show in groups That...

Reformat data to vertical format
Here is what I am trying to do. http://www.totalcontrolproducts.com/totalcontrolproducts_OLD/download/images/Untitled-1.gif I have about a thousand records that I need in a vertical format with normal shared field name. Any suggestions -- Psydwaz ----------------------------------------------------------------------- Psydwaze's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2437 View this thread: http://www.excelforum.com/showthread.php?threadid=37965 see Creating a Spreadsheet from Database data (#dbdata) http://www.mvps.org/dmcritchie/excel/snakecol.htm...

excel v4
Does anyone know when Version 4 of Excel was introduced and/or retired http://j-walk.com/ss/excel/index.htm -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference ---------------------------------------------------------------------------- <anonymous@discussions.microsoft.com> wrote in message news:042201c39e64$4f4d9ab0$a301280a@phx.gbl... > Does anyone know when Versi...