+ Reply to Thread
Results 1 to 2 of 2

How to break up a cell with 3 embedded (new line characters) value

  1. #1
    Phil
    Guest

    How to break up a cell with 3 embedded (new line characters) value

    Hello,

    I have a cell that has 3 values in it, and want to BREAK it up so that
    instead, I will get 2 MORE NEW rows, for a total now of 3 rows. I believe
    the values were inserted into the cell using the CTRL+ENTER key combination.
    There is a non-printable character (ANSI 0010) visible in the EDIT window to
    make the new line.

    This is how it looks below, graphically speaking (Row 1 contains the col.
    headings):

    Col- A Col - B Col - C Col-D
    Row-1 Tract_No Parcel_ID Owner
    Row-2 8-5-012-045 251254 Floyd Daniels
    7-5-065-012
    8-6-045-012
    Row-3 7-9-001-105 036584 James Clark
    Row-4 8-5-900-012 214566 Mary Jones

    As you can see, the 2nd row ACTUALLY contains 3
    values in the same cell. So, if you navigated to cell B2, and to hit F2 and
    look in the edit field above, it would look like this:

    8-5-012-045
    7-5-065-012
    8-6-045-012

    Where ALL 3 values are stacked up in the SAME cell window.

    So what I WANT to do is to take the value that resides in B2, and create 2
    NEW rows below it, then copy the other values from C2 and D2, and paste them
    on the new 2nd and 3rd rows.

    So my NEW output will look like this:

    Col- A Col - B Col - C Col-D
    Row-1 Tract_No Parcel_ID Owner
    Row-2 8-5-012-045 251254 Floyd Daniels
    Row-3 7-5-065-012 251254 Floyd Daniels
    Row-4 8-6-045-012 251254 Floyd Daniels
    Row-5 7-9-001-105 036584 James Clark
    Row-6 8-5-900-012 214566 Mary Jones

    FWIW: Incidentally, if you copied the cell B2 to a text editor, you will
    also see the 3 values like this, with the quotes at the end.

    "8-5-012-045
    7-5-065-012
    8-6-045-012"

    But if you paste it in Access it shows something like this:

    "8-5-012-045*7-5-065-012*8-6-045-012"

    How can this be done, if at all?

    TIA for your replies.

    Phil

  2. #2
    Jim May
    Guest

    Re: How to break up a cell with 3 embedded (new line characters) value

    I whipped up a rendition of this,, and will gladly send you on request,,
    Jim

    "Phil" <[email protected]> wrote in message
    news:[email protected]...
    > Hello,
    >
    > I have a cell that has 3 values in it, and want to BREAK it up so that
    > instead, I will get 2 MORE NEW rows, for a total now of 3 rows. I believe
    > the values were inserted into the cell using the CTRL+ENTER key
    > combination.
    > There is a non-printable character (ANSI 0010) visible in the EDIT window
    > to
    > make the new line.
    >
    > This is how it looks below, graphically speaking (Row 1 contains the col.
    > headings):
    >
    > Col- A Col - B Col - C Col-D
    > Row-1 Tract_No Parcel_ID Owner
    > Row-2 8-5-012-045 251254 Floyd Daniels
    > 7-5-065-012
    > 8-6-045-012
    > Row-3 7-9-001-105 036584 James Clark
    > Row-4 8-5-900-012 214566 Mary Jones
    >
    > As you can see, the 2nd row ACTUALLY contains 3
    > values in the same cell. So, if you navigated to cell B2, and to hit F2
    > and
    > look in the edit field above, it would look like this:
    >
    > 8-5-012-045
    > 7-5-065-012
    > 8-6-045-012
    >
    > Where ALL 3 values are stacked up in the SAME cell window.
    >
    > So what I WANT to do is to take the value that resides in B2, and create 2
    > NEW rows below it, then copy the other values from C2 and D2, and paste
    > them
    > on the new 2nd and 3rd rows.
    >
    > So my NEW output will look like this:
    >
    > Col- A Col - B Col - C Col-D
    > Row-1 Tract_No Parcel_ID Owner
    > Row-2 8-5-012-045 251254 Floyd Daniels
    > Row-3 7-5-065-012 251254 Floyd Daniels
    > Row-4 8-6-045-012 251254 Floyd Daniels
    > Row-5 7-9-001-105 036584 James Clark
    > Row-6 8-5-900-012 214566 Mary Jones
    >
    > FWIW: Incidentally, if you copied the cell B2 to a text editor, you will
    > also see the 3 values like this, with the quotes at the end.
    >
    > "8-5-012-045
    > 7-5-065-012
    > 8-6-045-012"
    >
    > But if you paste it in Access it shows something like this:
    >
    > "8-5-012-045*7-5-065-012*8-6-045-012"
    >
    > How can this be done, if at all?
    >
    > TIA for your replies.
    >
    > Phil




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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