+ Reply to Thread
Results 1 to 5 of 5

How to split Text in a Cell with line break to different columns

  1. #1
    Registered User
    Join Date
    04-14-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    97

    How to split Text in a Cell with line break to different columns

    Hi! I have a data In my cell B2 (column name Address)

    Albany Medical College
    47 New Scotland Avenue MC70
    Albany NY, 12208
    Ph: (612) 239-6115
    Fax: (612) 239-6118
    [email protected]


    I want to split the City / Phone / Fax / email to different columns,

    Please any help is much appreciated.

    A sample of my spreadsheet is attached.

    I have tried the formulas from some of the threads but no luck.

    Thanks in advance.

    Rev12
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: How to split Text in a Cell with line break to different columns

    Try this

    - Clear columns C to H
    - In Cell C2 use this formula
    =SUBSTITUTE(B2,CHAR(10),"!")
    - Copy down all the way
    - Copy Column C and 'paste special' -- 'values'
    - Select Range C2:C93
    - 'Data' ribbon -- text to columns -- Delimited -- Other -- Type ! -- Finish
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: How to split Text in a Cell with line break to different columns

    separator: press Ctrl+j
    in English
    - 'Data' ribbon -- text to columns -- Delimited -- Other -- press Ctrl+j -- Finish
    Last edited by tim201110; 06-01-2018 at 10:56 AM.

  4. #4
    Registered User
    Join Date
    04-14-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    97

    Re: How to split Text in a Cell with line break to different columns

    Thanks for the help, appreciated.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: How to split Text in a Cell with line break to different columns

    if you're able to use PowerQuery look at this one
    btw. some phones are not separated by LF in your source so text2columns isn't working well
    Attached Files Attached Files
    Last edited by sandy666; 06-01-2018 at 12:59 PM.

+ 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. Can I distribute text across columns when my delineator is a line break?
    By Joanne Marie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2015, 05:46 AM
  2. Replies: 2
    Last Post: 02-11-2015, 05:41 AM
  3. Replies: 4
    Last Post: 07-25-2013, 05:28 AM
  4. Split string by line break into array
    By kennychung in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2009, 12:18 PM
  5. break line in text cell from ASP
    By Lorenzo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-06-2006, 09:10 AM
  6. Split text like line wrap into multi columns
    By 1scant in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-16-2006, 03:53 PM
  7. Replies: 2
    Last Post: 06-14-2005, 02:05 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