Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2007
    Location
    London, Gtr London, United Kingdom
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question How to pad out emails?

    Howdie!

    The attached excel sheet has 3 sample email addresses (this is how they look when they're transported over from MS Outlook).

    How can I tweak this so each one appears on a new line?

    Thanks in advance.

    BJ.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    B.J.

    How are you transporting them? Via a File or by Code?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Jul 2007
    Location
    London, Gtr London, United Kingdom
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I am simply copying them from Outlook.

    And then pasting this into a an excel sheet.

    TIA.

    Bob

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    bj76
    Here is some code that will take the pasted string and separate it onto individual rows with the first email address overwriting the original paste. The code must be run with the pasted being the selected cell. It will cover up to 1000 email adresses but you can adjust the number to what you need. This code assumes you will always paste in the same format with each email starting and ending with a (') and separated with ;<space>.

    HTH,
    Maud


    Code:
    Public Sub test()
    emailString = ActiveCell.Value                            'sets pasted string value to a variable
    For i = 1 To 1000
        If emailString = "" Then Exit Sub                     'If the string is blank then the routine is complete
        Text = Left(emailString, InStr(emailString, "'") - 1) 'gets the left most email address
    ActiveCell.Value = Text                                   'enter the email address into the cell
        ActiveCell.Offset(1, 0).Select                        'move to next row
        emailString = Mid(emailString, Len(Text) + 5)         'set variable to remainder of string
    Next i
    End Sub
    Last edited by Maudibe; 2012-12-28 at 10:45.

  5. #5
    Lounger
    Join Date
    Jul 2007
    Location
    London, Gtr London, United Kingdom
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That is excellent and really appreciated.

    I wonder though if I could get away by doing this without coding.

    To be honest I have not used code before so that is another level for me.

    TIA.

    BJ.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    We just started a thread called VB corner http://windowssecrets.com/forums/sho...542#post884542 If you go there, you will find a simple, interactive tutorial to get you started. Once you read it, you will understand how to take the routines that members write and insert them in your worksheet. I have added the above to your sample spreadsheet from your original post

    Maud.
    Attached Files Attached Files
    Last edited by Maudibe; 2012-12-30 at 05:02. Reason: Adde revised spreadsheet

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •