+ Reply to Thread
Results 1 to 2 of 2

Data separated by line break into a column

  1. #1
    Registered User
    Join Date
    02-03-2021
    Location
    Sweden
    MS-Off Ver
    2013
    Posts
    1

    Question Data separated by line break into a column

    So I have a long extracted list (5600+ somewhat rows), with two columns, where A1 matches B1 etc. In some rare cases (around 200) the cell has multiple data separated by line break, both in A and B. If if have two lines in A I have two (matching) lines in B. And it can be up to 7 lines in one cell.

    I need to make this into a list, were A still matches B, but each cell only has 1 line. I have found a way to separate the multi lines (Text to Columns) with line break as separator, but that gives me several columns instead, and I need to do a lot of copy past, which is not durable in the long run since I do this each week.

    Any idea how to solve with formulas?

    Example:
    From this:
    A1
    A2
    B1
    B2
    A3 B3
    A4 B4
    A5
    A6
    A7
    B5
    B6
    B7
    A8
    A9
    B8
    B9

    Into this:
    A1 B1
    A2 B2
    A3 B3
    A4 B4
    A5 B5
    A6 B6
    A7 B7
    A8 B8
    A9 B9

  2. #2
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit - Win 11
    Posts
    915

    Re: Data separated by line break into a column

    Have a try with this macro, you can paste it in a standard vba module. I supposed that data starts in cell A1 and column A is master since you said that A1 matches B1 etc..
    Please Login or Register  to view this content.
    Last edited by rollis13; 02-03-2021 at 01:08 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. [SOLVED] Multiple matches in a cell separated by line break
    By Paulius03 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 01-22-2021, 08:26 AM
  2. [SOLVED] Color lines within the same cell separated by line break
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-27-2018, 10:38 PM
  3. [SOLVED] Count only data within a line break (alt-enter) with specific criteria in adjacent column
    By jparve3283 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-20-2016, 10:44 AM
  4. Display Column Values in ListBox (but need to line break every 3 columns)
    By lookingforhelp1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-16-2015, 11:18 AM
  5. [SOLVED] Line break in X-axis column label?
    By Ed in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-24-2006, 03:35 PM
  6. [SOLVED] line break in a cell-Is there a way to get the line break instead of a symbol?
    By luvgreen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-14-2005, 02:05 PM
  7. Line Drawing Between Separated Data Points ?
    By Robert11 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 05-09-2005, 12:09 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