Tom, the formula works great except for column 2. On this column I have data that is alpha numeric, The data could be from 1 digit up to 13 digits ( mostly it is an eight digit alpha field). On this field I need to complete the field with zeros to the left, for instance if the field is 123-W6789 ( 9 digits) I need convert to 0000123-W6789 (13 digits)
On the sheet the you provided the conversion works fine when this field is all numeric, but it does not work when it is alpha-numeric. (it does fill the left with zeros). Can you re-look at your formula and see what I need to change. Thanks a whole bunch On Thu, Apr 2, 2009 at 10:48 AM, Tom Jeffries <tjeff...@gmail.com> wrote: > The attached workbook contains a macro (CvtToText) that does what you > describe. You may want to change variable FilePath to define the location > of the text file. > If you have any questions let me know. > > Tom > > On Wed, Apr 1, 2009 at 4:43 PM, Cesar Delanoval <cdelano...@gmail.com>wrote: > >> >> I need to know if anyone can help with write a macro or script that >> will convert an Excel file to a .txt file after changing some of the >> formats on the data. >> >> this is what I have. >> >> 1 excel sheet with rows (they could vary) and 5 columns. >> The columns do not have headings. >> >> Column 1 = 13 digit field ( no changes needed for the field ) >> >> Column 2 = an alpha numeric field which can be from 1 digit to 13 >> digits. >> >> Column 3 = date in the MM/DD/YYYY format >> >> Column 4 = a numeric field that could be from 1 digit to 10 digits >> >> Column 5 = an amount with a 2 decimal representing a dollar amount >> ( could be from .01 to 999999.99 ) >> >> Now, I need to write a macro that will format the fields as follows >> >> Column 1 = no changes needed >> >> Column 2 = take whatever information on the field and fill to the left >> with zeros to complete a 13 digit alpha-numeric field. >> >> Column 3 = convert the date from MM/DD/YYYY to YYDDMM ( no hyphens or >> slashes ) >> >> Column 4 = take whatever information and pre-fill with zeros (to the >> left) to complete a 10 digit numeric field. >> >> Column 5 = take the amount in the format 99999.99 and eliminate the >> decimal to convert the amount to 8 digit numeric field without >> decimals. >> >> The process needs to repeat itself until no more data is found in the >> rows. Using the same order of the columns as described, put all the >> fields (after conversion) in the same order without spaces and convert >> (or save) the file into a .txt format. >> >> The end result should look like the lines below, where each number >> represents the column >> >> 11111111111112222222222222333333444444444455555555 >> 11111111111112222222222222333333444444444455555555 >> >> I know that this might sound like a lot of work, but I figure it is a >> good challenge for the gifted people on the group. >> >> I will be eternally grateful for any help on this subject. Thanks >> >> >> > > > > --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
