fixed column width with text string

I am importing a file into Excel.  This Excel file will be read by an 
automated system and needs to be formatted in a very precise way.  Each field 
from the data source (name, for example) is automatically inputed into a 
column in Excel.

In order for the automated system to read this file, each column must be a 
specific number of characters long, with blanks or zeroes making up the 
difference.  For example, if the name was "smith" and the "name" column is 
supposed to be 40 characters long, the remaining 35 characters would have to 
be accounted for somehow.  I want the final product in Excel to be a text 
string (no spaces, commas, columns, etc between each).  I will be running 
this report regularly, so the simpler the solution, the better.

Thanks!
0
Sarah2407 (145)
2/8/2005 7:05:05 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
123 Views

Similar Articles

[PageSpeed] 2

Hi, Sarah-
Here's one way: using your "Smith" example in a field that must be 40
characters long, enter the name Smith into cell A1.  In cell B1 type
this formula:
=A1&REPT(" ",40-LEN(A1))

This formula determines the length of the name in A1, subtracts that
number from 40, then adds that number of spaces to the name in A1.

You can copy that formula and Paste As values to convert the results
into text.

Dave O

0
CycleZen (674)
2/8/2005 9:09:56 PM
Reply:

Similar Artilces:

How do I fix this?
I used the Startup tool to setup my application but I unchecked the Menu Bar and the Shortcut Menu Bar checkboxes. I need to make some changes but I can't get the Tool menu back. Help Please. Thanks Hold down the Shift key when you open your database. Rob "Ayo" <Ayo@discussions.microsoft.com> wrote in message news:C3A9F20A-231C-42CA-80C4-59B836CFE1AB@microsoft.com... >I used the Startup tool to setup my application but I unchecked the Menu >Bar > and the Shortcut Menu Bar checkboxes. I need to make some changes but I > can't > get the Tool menu ba...

Need to convert 2002 back t0 while laptop is fixed
My laptop with Money 2002 died and had to be sent in for service which will probably be about a month. The good news is that I backup up all my data files the day before and burned them to CD. My office machine is running Windows 98 SE and Money 2001 but it will not read the 2002 files. I can't install XP because the machine will not support it. I used a friend's machine and can open my backup file but I don't want to leave my financial data there. Is there some way I can transfer the data to Excel or save it in a backwardly compatible format? I tried to synchronis...

Listbox columns
I have an access form with a Microsoft Forms 2.0 Listbox which I defined to have four columns. I am unable to add any item to anywhere apart from the first column. Listbox7.additem callnum ......works fine adds to the first column Listbox7.column(1).additem callnum ...... fails and I expected it to add to the second column Can anyone help with the method of adding data to the Listbox columns. Many thanks ST First of all, why are you using the Forms 2.0 listbox? What's wrong with the one that's built into Access that you'r...

Read text as a cell for a formula
I have two formulas in two cells, one spitting out a value for a colum (A) and the other a value for a row (2). In the a third cell I'm addin the letter and number together to create a cell address (A2). I want t use this information in a fourth cell for a formula (=A2*$B$10). But, how do I get Excel to recognize the text "A2" as a value for cell and then add A2 (or whatever the third cell may be) in to formula? Any help? Thanks, Nat -- Message posted from http://www.ExcelForum.com Hi Nate try =INDIRECT(A4)*$B$10 where A4 stores your cell reference as text, e.g. 'A2&...

column width changes
I have two seperate workbooks (Excel 2000)with exact same formatting. I save them as Excel 97 formats for use by colleague with older version of Excel. One workbook is okay but other will have one column width reduced, which causes cell to display ####. I've compared the workbooks one for one and cannot locate the problem. Any help? The first question I have is what's in that cell? Is it a date? Excel picks up several date formats from windows regional settings. For example, if you format as m/d/y and I use mm/dd/yyyy as my windows (8 digits and 2 slashes). If you use m/d/yy,...

Text box in master document moves out of print range
I have placed a text box at the bottom of the master page using publisher 2003. I have set it to be compatible with all my printers. However, when I set the print option to greyscale it shifts the box to a position lower than any printer can handle. It often looks ok in preview, but not on the hard copy itself. I have also noticed that the text moves in some cases witht he help of additional tabs. Why is this. My document is 200 pages long with 15 chapters. I have to change each chapter 4 times (left & right pages masters A & B) to overcome this problem. Your help would be much ...

Fixing column width
I have a spreadsheet in which I have set the widths of certain columns using Autofit. The problem is that I often have to add to the spreadsheet by importing data from a text file. Every time I do that, the column width changes to the width of the new data (which is usually narrower than what I wanted). Is there a way to prevent that from happening? Thanks, Cliff Lewis Assuming you're using the Data, Import Data command, when you get to the final "Import Data" dialog there is a "Properties" button (in Excel 2003 at least). One of those properties is Adjust column w...

sum column based on single cell match?
This seems so simple and yet the solution eludes me... In row 2 I want Excel to match a cell's contents (ex: find 3200). Once finding the column that matches, I want it to sum rows 3:94. Thank you in advance for your assistance. Something like this: =SUM(INDEX(A3:G94,,MATCH(3200,A2:G2,0)) In this example, you have columns A-G for your data. -- Best Regards, Luke M "adodson" <adodson@discussions.microsoft.com> wrote in message news:F7C768FE-2ED0-4FB4-881E-CDC9C7CE75DB@microsoft.com... > This seems so simple and yet the solution eludes me... ...

A scatter plot with a 2 column data source
Hi, I am using the Series tab (i have done a scatter plot) - i have put in 1 series of X and Y data. then i extended the "x values" data to include two columns of data. the scale is now different and my graph is in 2D still, i am wondering what relationship is now being described by my scatter plot. Thanks Excel doesn't know how to interpret your two-column X values, so it uses the values it always does when this happens: 1, 2, 3, etc. Excel uses these counting numbers if the X values consist of text (even a single text value in the range), or if no X values are even s...

Copy column headings AND formula totals to blank spreadsheet
Hi Everyone! I apologize that this was likely asked before but could someone show me how I can copy my 'column headings, column widths and totals formula' to a blank spreadsheet tab? I have set up budget spreadsheet where I input my receipts to keep track of monthly expenditures. Could someone explain how to copy the column headings, the column widths, AND the formulas of each column to a blank spreadsheet tab so each month will contain the 'same headings, same column widths and the same formula for each column'? Thanks so much for any help!!! katy Assuming you alr...

Row and column Headers formating
When I open a particular workbook i always find the headers are strikethrough, I need a help on how to remove this strikethrough, note this is not on the cells but rather on the headers i.e A, B , C etc This is set via menu Format/Style/Normal. --- Message posted from http://www.ExcelForum.com/ Salim Check out Format>Style>Normal. Modify the Font to eliminate the strikethrough. Gord Dibben Excel MVP On Tue, 15 Jun 2004 23:40:53 -0700, "Salim" <anonymous@discussions.microsoft.com> wrote: >When I open a particular workbook i always find the >headers are ...

Selecting a Range of Columns for Variable Rows
I want to do something like this: Select a sheet for row=3 to 17 select cells A:F copy the cells Select A2 on another sheet Paste what I copied. Shift down 1 row next row. I have verything down pretty well except selecting A:F for each row as it comes up. I would appreciate any help you can give me. Thanks. -- BillR It would be helpful if you post the code you have so far. It helps to fill in the gaps in your narrative. "BillR" <BillR@discussions.microsoft.com> wrote in message news:DCB74376-35D4-4426-8DFC-23B2565A9323@microsoft.com... >I want ...

The great disappearing hot fix
Has anyone experienced anything like this? Yesterday, one of our users complained to me about some quirk in GP eConnect processing that I had thought we fixed in December of 2007 via a Hot Fix to eConnect. Well, when I checked our server it was as if the hot fix was never installed (the eConnect release version was the basic 9.0.3.0, instead of 9.0.3.4). The Hot Fix was also missing from Add/Remove Programs. I went to reinstall the hot fix, and when the install program started it gave me the option to either Repair or Remove the hot fix. So, something was still present tha...

Cell Box - one of the 4 cell lines is open
What was the set of search "words" to find the answer... please? I have spent an hour. ( I don't want to tell you that I spent more time - mentioned). Oh yea! The lines are call borders and are part of cell formatting. Format > Cells... > Border > Outline > OK -- Gary''s Student - gsnu200826 "MUTTMIND" wrote: > What was the set of search "words" to find the answer... please? I have > spent an hour. ( I don't want to tell you that I spent more time - > mentioned). Oh yea! No worky. Excel 2007 - home tab - cells (ins...

Text in Publisher brohure
How do you adjust text color to make it translucent? See if this is what you want. Format Font Color drop down menu Fill effects tab Select a lighter shade. Then what ever you create, place it on top and do a Cntrl T to make the top translucent. "Marshams" <Marshams@discussions.microsoft.com> wrote in message news:547932F5-23F9-41BD-B620-B2269C12F22D@microsoft.com... > How do you adjust text color to make it translucent? ...

how do I transpose columns and rows
I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix You could add a column to your table, to count the donation occurrence, then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In...

Combination of text in 2 columns
Have a list of names-- first cell contains FirstName, MI; second cell contains LastName; Need to combine data into 3rd cell; output of 3rd cell: FirstName.LastName@XXX.com What is the easiest way to do this?? Thanks for your help! Maybe this with your names in A1 and b1 =A1&"."&B1&"@xxx.com" -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "eintpc5146" wrote: > Have a list of names-- > first cell...

Error importing dates from fixed width txt files
I'm trying to revise an existing Import Spec to include new fields added to a fixed width text file. If I import without making any revisions the dates import correctly. They are on the file as CCYYMMDD and the data field in the Spec is Date/Time and I've deselected 4 digit year. My new date fields were added to the text file as YYMMDD and I select Date/Time data field for the new fields. However, with the revisions made adding the new fields none of the date fields import now with the revised spec. Another user created the spec and we're both using Access 2003 with th...

Creating a chart with text not numbers
How do I create a chart that has text values (freshman, sophomore, junior, senior) instead of numbers (1, 2, 3, 4)? I have a column labeled "What is your class standing?" and below it is 166 rows where people selected "Freshman," "Sophomore," etc. I want to create a bar chart that counts how many people selected each of the four classes. Thanks, Alan First of all, you will have numbers to plot. The words are your category labels. Read how to turn a column of words like this into a histogram: http://peltiertech.com/Excel/Charts/Histograms.html - Jon...

how do i setup a database that columns add up at the end of the we
i am new at this so please send me any info to oldfartlink@hotmail.com Most people might say put a formula at the bottom of the list of numbers. For example, you have numbers in column B and they go from row 2 to row 154, in B155 (or anywhere else, pretty much), you could put this formula: =SUM(B2:B155) The 'problem' with that is that you can't just add to the bottom of the list, you have to keep inserting rows to push that formula on down the sheet. Try putting a SUM formula near the TOP of the column. Let's say you have the title for a column in row 1, then you sta...

Formatting an Excel column as a hyperlink
In my application I create and format Excel files using Excel automation. Is there a way to format a column in a spreadsheet as a hyperlink using Visual Basic? For example if a column contains the text j:\images\myimage.tif, can this be formatted as a hyperlink when the Excel file is being created and formatted? Bill Pete, I forgot to mention that I'm creating the Excel spreadsheets using VBA inside of my Access application, so that's why I'm asking it here. I'll also try the Excel newsgroup as you suggested. Bill "Pete D." <pduffy211@CAPS_AREcox.n...

Excel Column Charts
I am looking at trying to create a dual column chart which contains 8 sets of data, four of which are for plan data and four are for the actual data. Is it possible to create two combined columns, one for each set of data on the one graph. I cannot find any information to achieve this. Can anyone help. Thanks Ray One approach would be to plot two series, one for plan, the other for actual. Data: Plan Actual A [data] [data] B [data] [data] C [data] [data] D [data] [data] Select the range, create a clustered column chart. Each cluster will have two columns, pl...

"Click to add" column in Access 2010
Hi, In Access 2007 you are able to right-click on the "Click to add" column in Datasheet view of a table and hide it so that it's removed from view. Cannot find where I can do that in Access 2010... Are we stuck with that annoying extra column remaining in the view? :) My users will be using a form to enter data, so it's not that big of an issue, but I'd like to hide it if possible. If anyone knows a way to hide/remove it, please pass the info along. Thanks!! Tammy If I recall correctly, that only shows when you/your users are working directly in the ...

Fix
What does the word FIX mean on Excel 2000? It is in the lower right hand corner 3 boxes away from NUM, 4 boxes from CAPS. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ It means you have turned on fixed decimals places under tools>options>edit, so if you have 2 fixed decimal places and enter 20 in a cell it will change into 0.2 Just uncheck it if you don't want it -- Regards, Peo Sjoblom "zacharias" <zacharias.vdc4c@excelforum-nospam.com&...

Convert imported text to date time
I've read all the posts on this but I can't seem to find one that gives me the answer with a time field. I am importing from Excel as a .cvs and the date time comes in as a text field with 12/09/2009 0830. I need to convert this from text to an actual date/time. mm/dd/yyyy hh:mm preferably in military time. I need to calculate the minutes from Actual Departure time to Actual Arrival time. You guys will forever be my hero if you can give me the directions on doing this. Thanks! Cindy I pieced this together rapidly ad it seems to work. Function ConvDT(sFullDT) ...