+ Reply to Thread
Results 1 to 8 of 8

Mailing addresses in block for to fields

  1. #1
    Registered User
    Join Date
    07-15-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    19

    Mailing addresses in block for to fields

    I have all 26 MLB teams in this format:
    Baltimore Orioles
    Oriole Park
    333 West Camden Street
    Baltimore, MD 21201
    Phone: (410) 685-9800
    orioles.com

    Boston Red Sox
    Fenway Park
    4 Yawkey Way
    Boston, MA 02215
    Phone: (617) 267-9440
    redsox.com
    redsox.com/beisbol

    Chicago White Sox
    U.S. Cellular Field
    333 West 35th Street
    Chicago, IL 60616
    Phone: (312) 674-1000
    whitesox.com
    orgullosox.com

    and I would like to get them to the following format in a spreadsheet for doing a mail merge.

    City Team Stadium Address 1 City State Zip Phone Website1 Website2

    I can do the concatenation on a few teams by hand ( Red Sox, San Fancisco).... but having a tough time figuring this one out.

    Any help is appreciated.

    Thank you in advance

  2. #2
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Mailing addresses in block for to fields

    Do you mean you want change the column data to row data?
    Since you only have 26 data, you can just copy the whole address and then
    1. right click > paste special > transpose > ok
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++

  3. #3
    Registered User
    Join Date
    07-15-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    19

    Re: Mailing addresses in block for to fields

    I have tried this approach but transpose does not come up as an option after paste special.... I am still stymied. I cannot seem to attach a file either.

  4. #4
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Mailing addresses in block for to fields

    Try upload a sample workbook (remove sensitive data), with some sample result too. Thanks

    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

  5. #5
    Registered User
    Join Date
    07-15-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    19

    Re: Mailing addresses in block for to fields

    File is attached.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Mailing addresses in block for to fields

    Your data are not consistent, is hard to write VBA or function.

    1. If you wish to remove space in front/back of the text, can use "=trim(A1)"
    2. If you wish to shift column B data to column A, select column A and B, CTRL + G > Special > Blank > Move mouse to blank cell > right click > delete > Shift cells left
    3. Then you can try copy and paste transpose again.

  7. #7
    Registered User
    Join Date
    07-15-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    19

    Re: Mailing addresses in block for to fields

    ok, I will clean it up

  8. #8
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Mailing addresses in block for to fields

    Please Login or Register  to view this content.
    1. If you wish to remove space in front/back of the text, can use "=trim(A1)"
    2. If you wish to shift column B data to column A, select column A and B, CTRL + G > Special > Blank > Move mouse to blank cell > right click > delete > Shift cells left
    3. Please use this macro, try my best to organize the address for you. It will copy and paste your address start from column C.

    Attached sample workbook
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Inserting Commas Into Cells With Mailing Addresses
    By sdecker143 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-21-2014, 09:13 AM
  2. Replies: 1
    Last Post: 08-16-2012, 01:55 PM
  3. Replies: 1
    Last Post: 01-27-2012, 07:53 AM
  4. Inserting commas into cells with mailing addresses
    By maacmaac in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-02-2008, 11:22 AM
  5. How do I sort mailing addresses in Excel?
    By outfitterim in forum Excel General
    Replies: 2
    Last Post: 08-15-2006, 06:10 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1