Mailing addresses in block for to fields

**mussonip** · 09-22-2014, 09:21 PM

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

**wenqq3** · 09-22-2014, 10:07 PM

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

**mussonip** · 09-22-2014, 10:54 PM

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.

**wenqq3** · 09-22-2014, 11:01 PM

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.

**mussonip** · 09-22-2014, 11:17 PM

File is attached.

**wenqq3** · 09-22-2014, 11:28 PM

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.

**mussonip** · 09-22-2014, 11:44 PM

ok, I will clean it up

**wenqq3** · 09-22-2014, 11:50 PM

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