Excel Macro help needed!

Haven't been able to look at your file (Internet filters...) but, if you had the email address in cell A1, this will put it in the format you want and put the result in B1:

Please Login or Register  to view this content.

Originally Posted by charles_head

Hi,

I am hoping for an automated way to perform the following task. We need to do this task every day and at the moment it is a manual process envolving removing blanks spaces from a list of emails and several 'concatenate' and 'left' fuctions. The end results takes about half an hour and it is a perfect for a macro.

The problem is as follows:
I have a list of email addresses of varying character lengths. This list need to be processed into a txt document with a specific 60 character layout as below. The list in entered manually by end users and sometimes has spaces either beginning, withion or ending the email address. These blank spaces need to be removed before the layout below is processed.


Characters 1 > 50:
Starts with the email address. If the email address is shorter than 50 characters then the remaining characters are blank.

Characters 50 > 58:
Todays date in the format YYYYMMDD (eg: 20060727)

Characters 59 and 60:
Two blank characters end the sequence.


I have put an example of the final file on my personal webiste at http://www.digbyhead.com/sample.txt

This will save us hours a week if we could automate this process so any help is appreciated.

Best regards,
Charles Head
charles_head@hotmail.com

Easiest is probably to setup a Macro, set a Letter to trigger the macro, and replace the macro code with

Please Login or Register  to view this content.

This should split your data much the same way that selecting column A, then Data, Text to Columns, Fixed Width, set the widths, Finish, would achieve.

hth
---

note, there are three types of people in the world, those who can count, and those who can't.

Originally Posted by antoka05

You can try to use this code that will create the output file from your worksheet data_

Please Login or Register  to view this content.

I hope it's what you need.

Regards,
Antonio

Good point antoka05, it's going out not coming in, but does it need the two spaces for char 59-60 to make the length?

---