Adding leading zero to the date in the text files

**vinwin06** · 07-30-2010, 08:47 PM

Hi,

I need to add a leading zero before month in a text file, but the spacing should same as before after adding the zero also.

I tries using to open with excel and changed the format but after i did the test to columns the spacing of the old format is changed.

Ex:

BUARDC 04294008295 010321 6000 7/17/2010 0000354652 000224

This is the sample data, in this i need to add zero before that "7". but the format should be same like the spacing between the letters and all the numbers also.

If there is any macro coding available for this replacing the date format.

Attached the input file for your reference.

Can anyone help me on this how i can solve this issue.

Thanks in advance

**Leith Ross** · 07-30-2010, 09:47 PM

Hello vinwin06,

Check your post again. It doesn't look like the attachment came along.

**vinwin06** · 07-30-2010, 09:57 PM

Please find the attachment now!!

**vinwin06** · 07-30-2010, 10:00 PM

More information below ;

For ex:
BUARD2 04596009620 010327 14260 7/15/2010 0000354485 2799

In the above example there is a two space between the date and the next item, so the leading zero should be adjusted in the two spaces, so after adding the leading zero to the "month" (07) there should be one space between them.

After conversion the data should be like below ;

BUARD2 04596009620 010327 14260 07/15/2010 0000354485 2799

Kindly help me!!!

**MarvinP** · 07-30-2010, 10:07 PM

Open your .txt file in Notepad and Click on Edit -> Replace.
Type a "7/" in the Find what: box and "07/ in the Replace with: box.
Replace all and that should do it.

**vinwin06** · 07-30-2010, 10:11 PM

Hi Marvin,

Please see my above reply the spacing between the text important.

**Leith Ross** · 07-31-2010, 01:07 AM

Hello vinwin06,

I have tested this macro on your attachment and it works correctly. You will need to change the file path and possibly the file name in the macro.

Please Login or Register  to view this content.

**vinwin06** · 07-31-2010, 09:49 AM

Hi,

Thanks for your effort , but i am getting error message when i am try to change the path and file name the macro code looks like below

Please Login or Register  to view this content.

In this coding im getting error message path not found .

Can you please tel me where and all i need to change the path.

**Leith Ross** · 07-31-2010, 06:44 PM

Hello vinwin06,

The file path and file name are separate in the macro. You included the file name with the file path. See the bold corrections below.

Please Login or Register  to view this content.

**vinwin06** · 07-31-2010, 07:05 PM

Thank you so much , its working great. I appreciate all your efforts.

**vinwin06** · 07-31-2010, 07:48 PM

Hi Leith,

I need one more suggestions from you, if there is any way to run the same macro for two different file names. but both files are available in the same path .
Ex : the other file name is "ReconNet98.txt"

Can you help me in this.

**vinwin06** · 07-31-2010, 07:55 PM

Hi Leith,

I want to understand these codes briefly, could please explain these codes. Because i wan to learn how these codes finish the process .

Thanks for your help!!!

**protonLeah** · 07-31-2010, 08:57 PM

Replace:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

**martindwilson** · 07-31-2010, 09:26 PM

now there's a thing, been on this for days! never thought this could be done!

**Leith Ross** · 08-01-2010, 12:15 AM

Hello vinwin06,

I changed the macro to use the Open dialog. The directory is changed to where the files are located. You just select the text file you want to open and click "Open".

Please Login or Register  to view this content.

**vinwin06** · 08-01-2010, 03:54 AM

Hi Leith,

When i try use your codes given by you, its showing error of "method or data member not found".

**Leith Ross** · 08-01-2010, 11:52 AM

Hello vinwin06,

On which line does the error occur?

**vinwin06** · 08-01-2010, 12:04 PM

Im getting error on the below line ;

"Filename = Application.GetOpenFilename(FileFilters, 2)"

**Leith Ross** · 08-01-2010, 12:07 PM

Hello vinwin06,

The code runs fine on my computer. I am using Excel 2003. Are you using the same version of Excel?

**vinwin06** · 08-01-2010, 12:13 PM

Ya now is working for me also, thank you so much for your efforts.

Can you explain these codes briefly , because i am curious about to learn this codings.

**Leith Ross** · 08-01-2010, 12:37 PM

Hello vinwin06,

I will need some time to write the explanations. I will try to post that later today.

**vinwin06** · 08-01-2010, 12:50 PM

Thanks Leith.

**vinwin06** · 08-01-2010, 06:31 PM

HI Leith,

I am waiting for your reply!!!

**vinwin06** · 08-03-2010, 09:34 PM

Hi leith,

when i can expect reply from you????

**jolivanes** · 08-03-2010, 09:51 PM

Dear vinwin06.
Please be patient. The people helping you do that in their free time and at no cost to you. I am sure if you paid them you'd have your answer right away but you can't expect volunteers to jump for you when you say so.
Be kind to these helpful people please.

Regards
John

**vinwin06** · 08-03-2010, 10:04 PM

Ok John, i am not forcing leith to give me the reply immediately. I am eager to know about it thats why, its just reminder not anything else

Thanks

**MarvinP** · 08-03-2010, 11:35 PM

I just didn't understand why my first answer didn't work. I suggested using Notepad and the Replace function. I now see I needed a second step. After replacing all "7/" with "07/" you should do a second replace of
"2010 0000" with "2010 0000" That should keep you from running all that code. That would take the the 2010<sp><sp>0000 and make them all 2010<sp>0000.
Save the text file and go on to the next one.

**MarvinP** · 08-03-2010, 11:56 PM

I messed up. I didn't see there was a 7/17/2010 that created a problem. The fix is to search for:
<sp>7/ and replace it with <sp>07/ This would then do your problem correctly and not pick up the 7/17/2010. You need to compress the two spaces between 2010 and 0000 as shown above.

**martindwilson** · 08-04-2010, 03:10 AM

@MARVINP
see this earlier thread http://www.excelforum.com/excel-misc...inal-file.html obviously op wanted a macro solution all along

Adding leading zero to the date in the text files

LinkBack

Thread Tools

Rate This Thread

Display

Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Re: Adding leading zero to the date in the text files

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions