+ Reply to Thread
Results 1 to 3 of 3

Break apart a single cell to create a mailing list

  1. #1
    Registered User
    Join Date
    09-17-2014
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    1

    Break apart a single cell to create a mailing list

    We have software for our medical office that generates a patient list. Unfortunately, the XLS document that is generated places the patients name and address into a single field. I need to break that single field/cell into LAST NAME, FIRST NAME, ADDRESS LINE 1, ADDRESS LINE 2, CITY, STATE, ZIP.

    For example, the entire cell might read
    Bob Smith
    123 Main Street
    Apt A
    Mytown, NY 12345


    Or possibly:
    Bob Smith
    123 Main Street
    Mytown, NY 12345


    I am completely at a loss how to do this. Any help is greatly appreciated.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Break apart a single cell to create a mailing list

    Try this formula

    in B1 and pull formula to the right until you see blanks

    =TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))

    A
    B
    C
    D
    E
    1
    Bob Smith
    123 Main Street
    Apt A
    Mytown, NY 12345
    Bob Smith 123 Main Street Apt A Mytown, NY 12345
    2
    Bob Smith
    123 Main Street
    Mytown, NY 12345
    Bob Smith 123 Main Street Mytown, NY 12345
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    4,887

    Re: Break apart a single cell to create a mailing list

    Frist, select your column of addresses, and use Text to column, delimited, and select the other box, and hold down the ALT key as you type 0010 on the numeric keypad, then click OK.

    Then, in cell E2, use

    =IF(COUNTA(A2:D2)=3,"",C2)

    and in F2, use

    =IF(COUNTA(A2:D2)=3,C2,D2)

    and copy down to match. Then copy E:F and paste special values, then delete columns C and D.

    Then insert a new column B and use text to columns on A to split the first and last names. Then use Text to columns on the city state zip to split those, and maybe use formulas like those above to extract the state and zip and recombine the city names if they are multi-word names.
    Bernie Deitrick
    Excel MVP 2000-2010

+ 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. Create mailing list from excel file,
    By wtowers in forum Excel General
    Replies: 2
    Last Post: 08-30-2014, 11:59 AM
  2. Create a mailing list from a master list
    By ajob in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2014, 08:56 PM
  3. Print single line from mailing list
    By rbdaves in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2011, 12:26 AM
  4. [SOLVED] RE: i want to create a simple mailing list can u help
    By Gary Brown in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-18-2005, 01:06 PM
  5. i want to create a simple mailing list can u help
    By tiner1964 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-18-2005, 01:06 PM

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