+ Reply to Thread
Results 1 to 8 of 8

Inserting Commas Into Cells With Mailing Addresses

  1. #1
    Registered User
    Join Date
    05-13-2014
    Location
    NC
    MS-Off Ver
    365
    Posts
    21

    Inserting Commas Into Cells With Mailing Addresses

    I receive a report that is generated by another program. One column contains the address, city, state, and zip (5 and 10 digit) with no coma separators. I was wondering if anyone can think of a macro that would add comas before the Zip, state, and city so that I can use the text to columns tool in excel.

    here is an example of the column

    7935 COUNCIL PL SUITE 200 MATTHEWS NC 28105
    223 WRENWOOD LN CHARLOTTE NC 28211
    1125 BIRCH ST APT A ROGERS CITY MI 49779-1406
    PO BOX 1376 NEW YORK NY 10018
    428 C ST APT 301 SAN DIEGO CA 92101
    4682 MISSION BV SAN DIEGO CA 92109

    I need it to look like this:

    7935 COUNCIL PL SUITE 200 ,MATTHEWS ,NC ,28105
    223 WRENWOOD LN ,CHARLOTTE ,NC ,28211
    1125 BIRCH ST APT A ,ROGERS CITY ,MI ,49779-1406
    PO BOX 1376 ,NEW YORK ,NY ,10018
    428 C ST APT 301 ,SAN DIEGO ,CA ,92101
    4682 MISSION BV ,SAN DIEGO ,CA ,92109

    Then I can run the text to columns and it will split the text into 4 columns.

    Thanks in advance for any help that I can receive on this.

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Inserting Commas Into Cells With Mailing Addresses

    Assuming the city, state, zip and in columns D, E, F respectively.

    Please Login or Register  to view this content.
    Edit: Nevermind. I just read your post again and I think you are saying your data is in one cell. I have no desire to tackle that problem at this moment.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Inserting Commas Into Cells With Mailing Addresses

    Assuming the data in col.A and put the results in col.B
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-13-2014
    Location
    NC
    MS-Off Ver
    365
    Posts
    21

    Re: Inserting Commas Into Cells With Mailing Addresses

    Thanks so Much Jindon. That worked exctly like I hoped it would. This will be a huge timesaver. Thank you.

  5. #5
    Registered User
    Join Date
    05-13-2014
    Location
    NC
    MS-Off Ver
    365
    Posts
    21

    Re: Inserting Commas Into Cells With Mailing Addresses

    jindon,

    Your code works great. I want to tweak it a little but i have clue how it works. I want it to first look and see if there is 4 sections
    (Attn: C/O ADVANTAGEIQ-MAILSTOP PO BOX 2440 SPOKANE WA 99210).
    If there are four sections then it should look like this:
    Attn: C/O ADVANTAGEIQ-MAILSTOP ,PO BOX 2440 ,SPOKANE ,WA ,99210 .

    If there are not four sections then the code would run as is with the exception of adding a comma at the beginning of the address so it would look like this:
    ,18915 CLOVERSTONE CR ,CORNELIUS ,NC ,28031 .

    I would greatly appreciate it if you could help improve this code.

    Thanks in advance
    sdecker143

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Inserting Commas Into Cells With Mailing Addresses

    You need to show me all the patterns that you have in a workbook, so that I can tell you if I can help you or not.

  7. #7
    Registered User
    Join Date
    05-13-2014
    Location
    NC
    MS-Off Ver
    365
    Posts
    21

    Re: Inserting Commas Into Cells With Mailing Addresses

    Jindon,

    I was able to figure something else out that will do what I need it to do. Thank for your code. This is a huge help.

    sdecker143
    Last edited by sdecker143; 05-21-2014 at 09:41 AM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Inserting Commas Into Cells With Mailing Addresses

    It is quite difficult to determine which is Col.A or B....

    Await for the file.

+ 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. Replies: 5
    Last Post: 01-17-2014, 05:56 PM
  2. Adding commas "," in email addresses for sending BULK Email stored cellwise..!
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-26-2011, 09:50 AM
  3. Inserting commas into cells with mailing addresses
    By maacmaac in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-02-2008, 11:22 AM
  4. [SOLVED] How do I sort mailing addresses in Excel?
    By outfitterim in forum Excel General
    Replies: 2
    Last Post: 08-15-2006, 06:10 AM
  5. [SOLVED] how do i convert MS Word mailing labels into an Excel mailing lis.
    By unrhyll in forum Excel General
    Replies: 1
    Last Post: 02-03-2005, 09:06 PM

Tags for this Thread

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