Attached is an updated version that handles the alphanumeric data in column 2 correctly. Tom
On Thu, Apr 2, 2009 at 7:56 PM, Cesar Delanoval <cdelano...@gmail.com>wrote: > 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
CvtToText2.xls
Description: MS-Excel spreadsheet
